/*
 * Created on Mar 4, 2004
 */
package com.scalar.chiptrack.operations.packingslip.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

import org.apache.struts.util.LabelValueBean;
import org.apache.struts.util.MessageResources;

import com.scalar.ScalarException;
import com.scalar.chiptrack.adaptor.InventoryLot;
import com.scalar.chiptrack.commons.Address;
import com.scalar.chiptrack.commons.ClientInfo;
import com.scalar.chiptrack.commons.Constants;
import com.scalar.chiptrack.commons.Labels;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DBXException;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.operations.packingslip.CustomerShippingLineItems;
import com.scalar.chiptrack.operations.packingslip.CustomerShippingLotNumberData;
import com.scalar.chiptrack.operations.packingslip.FinalInventoryData;
import com.scalar.chiptrack.operations.packingslip.PackingSlip;
import com.scalar.chiptrack.operations.packingslip.forms.CommercialInvoiceForm;
import com.scalar.chiptrack.operations.packingslip.forms.PackingSlipForm;
import com.scalar.chiptrack.operations.packingslip.forms.SearchPackingSlipForm;
import com.scalar.chiptrack.operations.packingslip.forms.ShippingAdviseForm;
import com.scalar.chiptrack.operations.process.dao.LotDAO;
import com.scalar.chiptrack.operations.vendor.VendorAddress;
import com.scalar.chiptrack.operations.vendor.dao.VendorDAO;
import com.scalar.chiptrack.sales.ContactPerson;
import com.scalar.chiptrack.sales.Customer;
import com.scalar.chiptrack.sales.LineItem;
import com.scalar.chiptrack.sales.SalesOrder;
import com.scalar.chiptrack.sales.dao.CustomerDAO;
import com.scalar.chiptrack.sales.dao.NewSalesOrderDAO;
import com.scalar.chiptrack.sales.dao.SalesOrderDAO;
import com.scalar.chiptrack.users.Functionality;
import com.scalar.chiptrack.users.User;
import com.scalar.chiptrack.users.dao.UserDAO;
import com.scalar.chiptrack.utils.Mailer;
import com.scalar.chiptrack.utils.SqlQueryMappings;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.chiptrack.utils.ConfigManager;

/**
 * @author Ravikanth
**/
public class NewPackingSlipDAO implements Constants
{
    // This returns all the LineItem Details for Generate Packing Slip
    public static ArrayList loadLineItemDetails(int salesOrderId, int vendor_address_Id, String coolStatus, String scheduledBefore) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        String dbCool_Status = null ;

        ArrayList lineItemList = new ArrayList();

        try
        {
            conn 	= DataSource.getConnection();
            pstmt	= conn.prepareStatement( SqlQueryMappings.get("operations.packingslip.loadApprovedLineItemDetails") );
            //pstmt	= conn.prepareStatement( "{ call sp_get_All_Approved_Line_Items( ?, ? ) }" );
            pstmt.setInt( 1, salesOrderId );
            pstmt.setInt( 2, vendor_address_Id );
            pstmt.setString ( 3, scheduledBefore );

            rset 	= pstmt.executeQuery();
            CustomerShippingLineItems cslt = null;
            while(rset.next())
            {
                dbCool_Status = rset.getString( "COOL_Status" );  // Added by Krishna 28 June 2004 , to get LineItems according to Status.
                if ( dbCool_Status != null && dbCool_Status.trim().length() > 0 && dbCool_Status.equals(coolStatus) )
                {
                    cslt = new CustomerShippingLineItems();
                    cslt.setLineItemId	  ( rset.getInt( "lineItemId" ) );
                    cslt.setQtyToShip	  ( rset.getInt( "qtyToShip" ) );
                    cslt.setSalesOrder_ID ( rset.getInt( "SalesOrder_ID" ) );
                    cslt.setLineitemNumber( rset.getInt( "COOL_Line_Item_No" ) );
                    cslt.setProduct_ID	  ( rset.getInt( "Product_ID" ) );
                    cslt.setCool_Qty	  ( rset.getInt( "COOL_Qty" ) );
                    cslt.setCool_Unit_Cost( rset.getFloat( "COOL_Unit_Cost" ) );
                    cslt.setCool_Amount	  ( rset.getFloat( "COOL_Amount" ) );
                    cslt.setQtyPulled	  ( 0 );
                    cslt.setOrginalQtyPulled( 0 );

                    cslt.setCool_Requested_Date	( StringUtils.formatDateToString( rset.getDate( "COOL_Requested_Date" ), null ) );
                    cslt.setCool_Booking_Date	( StringUtils.formatDateToString( rset.getDate( "COOL_Booking_Date" ), null ) );
                    cslt.setCool_Sugg_Schedule_Date_With_Substitutables( StringUtils.formatDateToString( rset.getDate( "COOL_Sugg_Schedule_Date_With_Substitutables" ), null ) );
                    cslt.setCool_Sugg_Schedule_Date_Without_Substitutables( StringUtils.formatDateToString( rset.getDate( "COOL_Sugg_Schedule_Date_Without_Substitutables" ), null ) );
                    cslt.setCool_Schedule_Date	( StringUtils.formatDateToString( rset.getDate( "COOL_Schedule_Date" ), null ) );
                    cslt.setCool_Revised_Date	( StringUtils.formatDateToString( rset.getDate( "COOL_Revised_Date" ), null ) );
                    cslt.setCool_Is_Cancel	( rset.getBoolean( "COOL_Is_Cancel" ) );
                    cslt.setCool_Status		( dbCool_Status );
                    cslt.setCool_Taxable	( rset.getBoolean( "COOL_Taxable" ) );
                    cslt.setCool_Description( rset.getString( "COOL_Description" ) );
                    cslt.setCool_PO_Line_Item_No( rset.getInt( "COOL_PO_Line_Item_No" ) );
                    cslt.setPart_Number		( rset.getString( "Part_Number" ) );
                    cslt.setMkt_Part_Number	( rset.getString( "Mkt_Part_Number" ) );
                    cslt.setCustomer_Part_Number( rset.getString("Customer_Part_Number") );
                    cslt.setQty_Available 	( rset.getInt( "Qty_Available" ) );
                    //cslt.setSubstitutablePartNumbers(  loadShippedPartNumbersByCsLineItemId ( null , -1 , cslt.getPart_Number(), "open" ) );
                    cslt.setSubstitutablePartNumbers(  loadSubstitutablePartNumbersByCsLineItemId ( null , -1 , cslt.getPart_Number(), "open", cslt.getProduct_ID() ) );
                    cslt.setCustomer_Label_Number( rset.getString("Customer_Label_Number") );
                    cslt.setParentPartNumber( rset.getString( "Parent_Part_Number" ) );
                    cslt.setUnits(ConfigManager.getUOMForPackingslip());

                    lineItemList.add( cslt );
                }
            }

        }
        catch (Exception e) {
            throw new ScalarException ("Could not retrieve PackingSlip list from database", e);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
        return lineItemList;
    }

    /**
     * This method is to Insert values into
     * CST, CSLIT, CSLNT tables and update the Inventory_Lot table balance
     * Release the lots for the sales Order in Reserved_lots table
     * 1. Used in SavePackingSlipAction
     */
    public static int saveCustomerShippingInfo(PackingSlipForm packingSlipForm, HashMap finalInventoryMap, String currentUser, boolean confirmPackingSlip) throws ScalarException
    {
        Connection conn 		= null;
        PreparedStatement pstmt = null;
        ResultSet rset 			= null;
        boolean success 		= false;
        int customerShippingId 	= -1;
        int shippingAddrId      = -1;
        int shipperAddrId       = -1;
        int billAddrId          = -1;
        Address address         = null;
        VendorAddress vAddress  = null;
        int cstRandomKey        = -1;
        double dWeight          = 0.0;
        double grossWeight      = 0.0;

        DBTransaction dbTransaction = null;

        try
        {
            //HashMap InventoryDataMapDB = getFinalInventoryMapByLineItemId( NewPackingSlipDAO.loadLotNumberDataByCustomerShippingId ( packingSlipForm.getCustomerShippingId() ) );
            //mergeDBInventoryDataMapIntoExistMap( finalInventoryMap , InventoryDataMapDB );

            //if( /*packingSlipForm.isDropShippment() &&*/ ! packingSlipForm.getStatus().equalsIgnoreCase( "shipped" ) )
            //{
                // Setting the LotIDs for the New Lots entered through Drop Shipment
           //     fetchLotIdForNewLots( dbTransaction, finalInventoryMap );
            //}   

            //This code moved from end of Method to here because getting problem with transcation Obj in GP Integration.
            String destinationPort = null;
            if( packingSlipForm.getShippingToAddressId() != -1 )
            {
                Address shippingAddress = UserDAO.loadAddressById( packingSlipForm.getShippingToAddressId() );

                if( shippingAddress.getCountry() != null && shippingAddress.getCountry().trim().length() != 0 )
                {
                    destinationPort = shippingAddress.getCountry();
                }
            }

            vAddress=VendorDAO.getVendorNameAndAddress( packingSlipForm.getShippingFromLocationId() ); //Shipper or Vendor Address Object

            //SETTING THE ODM_PART_NUMBER TO LINE ITEMS
            NewSalesOrderDAO.loadCSLineItemsWithCustomerAndOdmPartNumbers ( packingSlipForm.getLineitems(), packingSlipForm.getSalesOrderId() );

            conn = null;

            dbTransaction = DBTransaction.getInstance();

            if( dbTransaction != null )
            {
                dbTransaction.begin();
            }

            String packingSlipStatus = ( (confirmPackingSlip) ? CUSTOMER_SHIPPING_STATUS_SHIPPED : CUSTOMER_SHIPPING_STATUS_ISSED );

            try
            {
                dWeight = Double.parseDouble( packingSlipForm.getWeight() );
            }
            catch( Exception e )
            {
                dWeight = 0.0;
            }
            try
            {
                grossWeight = Double.parseDouble( packingSlipForm.getGrossWeight() );
            }
            catch( Exception e )
            {
                grossWeight = 0.0;
            }

            while( cstRandomKey == -1 )
            {
                cstRandomKey = getUniqueRandomKey(dbTransaction) ;
            }

            // int cstRandomKey = new Random(System.currentTimeMillis()).nextInt();

            address =  packingSlipForm.getShippingAddressObject();

            shippingAddrId = saveAddress( 		dbTransaction,
                                                -1,                     //-1 means insert new address
                                                address.getAddress1(),
                                                address.getAddress2(),
                                                address.getAddress3(),
                                                address.getAddress4(),
                                                address.getCity(),
                                                address.getState(),
                                                address.getZipCode(),
                                                address.getCountry(),
                                                address.getPhoneNumber(),
                                                address.getFaxNumber(),
                                                address.getEmail(),
                                                address.getContactPerson(),
                                                address.getCellNumber(),
                                                true, packingSlipForm.getCustomerNumber()  , null, address.getTagName());

           address = null;

           shipperAddrId = saveAddress( dbTransaction,
                                                -1,                     //-1 means insert new address
                                                vAddress.getAddress1(),
                                                vAddress.getAddress2(),
                                                vAddress.getAddress3(),
                                                vAddress.getAddress4(),
                                                vAddress.getCity(),
                                                vAddress.getState(),
                                                vAddress.getZipCode(),
                                                vAddress.getCountry(),
                                                vAddress.getPhoneNumber(),
                                                vAddress.getFaxNumber(),
                                                vAddress.getEmail(),
                                                vAddress.getContactPerson(),
                                                vAddress.getCellNumber(),
                                                true, packingSlipForm.getCustomerNumber(), null, vAddress.getTagName());
            address = null;

            address = packingSlipForm.getBillingAddressObject();

            billAddrId = saveAddress( dbTransaction,
                                                -1,                     // -1 means insert new address
                                                address.getAddress1(),
                                                address.getAddress2(),
                                                address.getAddress3(),
                                                address.getAddress4(),
                                                address.getCity(),
                                                address.getState(),
                                                address.getZipCode(),
                                                address.getCountry(),
                                                address.getPhoneNumber(),
                                                address.getFaxNumber(),
                                                address.getEmail(),
                                                address.getContactPerson(),
                                                address.getCellNumber(),
                                                true, packingSlipForm.getCustomerNumber() , null, address.getTagName());
            address = null;
            //System.out.println("----Shipping AddId = " + shippingAddrId + " Vendor AddId = " + shipperAddrId + " Billing AddId = " + billAddrId);
            if( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            // Step 1
            // Insert Customer Shipping Info into Customer Shipping Table
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.packingslip.savePSinCustomerShippingTable" ) );
            pstmt.setString ( 1, packingSlipForm.getLabel() );
            pstmt.setInt 	( 2, packingSlipForm.getSalesOrderId() );
            pstmt.setString ( 3, packingSlipForm.getContactPerson() );
            pstmt.setString ( 4, packingSlipForm.getTrackingAsString() );
            pstmt.setString ( 5, packingSlipForm.getFob() );
            pstmt.setInt 	( 6, packingSlipForm.getNoOfBoxesAsInt() );
            pstmt.setFloat	( 7, 0.0f );
            pstmt.setString ( 8, packingSlipStatus );
            //pstmt.setInt 	( 9, packingSlipForm.getShippingFromAddressId() );
            pstmt.setInt 	( 9, shipperAddrId );
            //pstmt.setInt 	( 10, packingSlipForm.getShippingToAddressId() );
            pstmt.setInt 	( 10, shippingAddrId );
            if( packingSlipForm.getShippedVia().length() > 50 )
                pstmt.setString ( 11, packingSlipForm.getShippedVia().substring( 0, 50 ) );
            else
                pstmt.setString ( 11, packingSlipForm.getShippedVia() );
            pstmt.setString ( 12, packingSlipForm.getShippingAccount() );
            pstmt.setString ( 13, packingSlipForm.getShippingInstructions() );
            pstmt.setString ( 14, packingSlipForm.getShipDate() );
            //pstmt.setString ( 15, tempWeight);
            pstmt.setDouble( 15, dWeight);
            pstmt.setString ( 16, currentUser );
            pstmt.setString ( 17, packingSlipForm.getInvNo() );
            pstmt.setInt 	( 18, packingSlipForm.isDropShippment() ? 0 : 1 );
            pstmt.setString ( 19, packingSlipForm.getComments() );
            pstmt.setInt 	( 20, billAddrId );
            pstmt.setInt    ( 21, packingSlipForm.getShippingFromLocationId() );
            pstmt.setInt    ( 22, cstRandomKey );
            pstmt.setString ( 23, packingSlipForm.getRepOffice() );
            pstmt.setString ( 24, packingSlipForm.getSupplierNumber() );
            pstmt.setString ( 25, packingSlipForm.getOdmSupplierNumber() );
            pstmt.setInt    ( 26, packingSlipForm.getOdmId() );
            pstmt.setString ( 27, packingSlipForm.getFreightInstructions() );
            pstmt.setDouble ( 28, grossWeight );
            pstmt.setString ( 29, packingSlipForm.getPackageDimension() );
            pstmt.setString ( 30, packingSlipForm.getWeightType() );
            pstmt.setString ( 31, packingSlipForm.getUomPkgDimension() );
            pstmt.setString ( 32, packingSlipForm.getMawb() );
            pstmt.setString ( 33, packingSlipForm.getHawb() );
            pstmt.setString ( 34, packingSlipForm.getFlightNumberOne() );
            pstmt.setString ( 35, packingSlipForm.getFlightNumberTwo() );
            pstmt.setString ( 36, packingSlipForm.getEtd() );
            pstmt.setString ( 37, packingSlipForm.getEta() );
            
            pstmt.executeUpdate();
            pstmt.close();
            pstmt = null;

            // Step 2
            // Get the customerShippingId from customer_shipping table
            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "operations.packingslip.insertCustomerShippingInfo.readIdentity" ) );
            rset = pstmt.executeQuery();

            if (rset.next() != false)
            {
                customerShippingId =  rset.getInt ("CustomerShipping_ID");
            }
            rset.close();
            pstmt.close();
            pstmt = null;

            // Step 3
            // Insert into Customer_shipping_line_Item table
            pstmt = conn.prepareStatement ( SqlQueryMappings.get( "operations.packingslip.savePSinCustomerShippingLineItemTable" ) );
            //pstmt = conn.prepareStatement ( "INSERT INTO Customer_Shipping_Line_Item_Table( CustomerShipping_ID, LineItem_ID, CSLIT_Qty_Shipped, CSLIT_Shipped_Date, CSLIT_Status, CSLIT_Description, CSLIT_Units, CSLIT_Cust_Part_Number) VALUES ( ?, ?, ?, ?, ?, ?, ?, ? )" );

            CustomerShippingLineItems lineItem = null;
            ArrayList lineItems = packingSlipForm.getLineitems();
            int lineItemCount 	= lineItems.size();
            String  shippedPartNumbers = null;
            int selectedLineitemCount = 0;

            for ( int i = 0; i < lineItemCount; i++ )
            {
                lineItem = ( CustomerShippingLineItems ) lineItems.get (i);
                if( lineItem.getQtyPulled() > 0 )
                {
                    pstmt.setInt 	( 1, customerShippingId );
                    pstmt.setInt 	( 2, lineItem.getLineItemId() );
                    pstmt.setInt 	( 3, lineItem.getQtyPulled() );
                    pstmt.setString ( 4, packingSlipForm.getShipDate() );
                    pstmt.setString ( 5, CUSTOMER_SHIPPING_STATUS_ISSED );
                    pstmt.setString ( 6, lineItem.getCool_Description() );
                    pstmt.setString ( 7, lineItem.getUnits() );
                    pstmt.setString ( 8, lineItem.getCustomer_Part_Number() );
                    shippedPartNumbers = getShippedPartNumberString( lineItem.getShipped_Part_Number(), null );
                    if( shippedPartNumbers != null && shippedPartNumbers.trim().length() > 0 && ! shippedPartNumbers.equals( lineItem.getPart_Number() ) )
                    {
                        pstmt.setString	( 9, shippedPartNumbers );
                    }
                    else
                    {
                        pstmt.setString	( 9, null  );
                    }
                    pstmt.setString ( 10, lineItem.getCustomer_Label_Number() );//Added by Krishna Prasad on 23-11-2004
                    pstmt.setString ( 11, lineItem.getOdm_Part_Number() );
                    pstmt.addBatch();
                    selectedLineitemCount++;
                }
            }
            pstmt.executeBatch();
            pstmt.close();
            pstmt = null;

            // Step 4
            // Get CSLIT_ID from Customer_Shipping_line_item table

            int cslitId = -1;
            //pstmt = conn.prepareStatement ( "SELECT @@IDENTITY as CSLIT_ID" );
            pstmt = conn.prepareStatement ( SqlQueryMappings.get( "operations.packingslip.saveCustomerShippingInfo.readIdentity" ) );
            rset = pstmt.executeQuery();

            if ( rset.next() != false )
            {
                cslitId =  rset.getInt ( "CSLIT_ID" );
            }
            rset.close();
            pstmt.close();
            pstmt = null;

            // Step 5
            // Insert into Customer_Shiping_Lot_Number Table
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.packingslip.saveCustomerShippingLotNumberInfo" ) );
            //pstmt = conn.prepareStatement( "{ call sp_save_customer_shipping_lot_number_data( ?, ?, ? ) }" );

            int seedCslitId = ( cslitId - selectedLineitemCount ) + 1;

            for ( int i = 0; i < lineItemCount; i++ )
            {
                lineItem = ( CustomerShippingLineItems ) lineItems.get (i);
                if( lineItem.getQtyPulled() > 0 )
                {
                    lineItem.setCustomerShippingLineItemId( seedCslitId++ );
                }
            }

            int finalInventoryDataListSize = -1;
            ArrayList finalInventoryDataList = null;
            FinalInventoryData inventoryData = null;

            for( int i=0; i < lineItemCount; i++ )
            {
                if( ( ( CustomerShippingLineItems ) lineItems.get(i) ).getQtyPulled() > 0 )
                {
                    lineItem = ( CustomerShippingLineItems ) lineItems.get (i);

                    if( finalInventoryMap != null )
                    {
                        finalInventoryDataList = ( ArrayList ) finalInventoryMap.get ( ( ( CustomerShippingLineItems ) lineItems.get(i) ).getLineItemId()+"" );

                        if( finalInventoryDataList != null )
                        {
                            finalInventoryDataListSize = finalInventoryDataList.size();

                            for( int j = 0; j < finalInventoryDataListSize; j++ )
                            {
                                inventoryData = (FinalInventoryData) finalInventoryDataList.get ( j ) ;
                                if( inventoryData.getQuantityPull() > 0 )
                                {
                                    pstmt.setInt ( 1, lineItem.getCustomerShippingLineItemId() );
                                    pstmt.setInt ( 2, inventoryData.getLotId() );
                                    pstmt.setInt ( 3, inventoryData.getQuantityPull() );

                                    pstmt.addBatch();
                                }
                            }
                            pstmt.executeBatch();
                            pstmt.clearBatch();
                        }
                    }
                }
            }

            pstmt.close();
            pstmt = null;


            // Step 6
            // Update 'Balance' and 'Shipping' Fields in Inventory_Lot Table
            //pstmt = conn.prepareStatement ( "{call sp_update_balance_qty_in_inventory_lot (?, ?, ?, ?) }" );
            pstmt = conn.prepareStatement ( SqlQueryMappings.get( "operations.packingslip.updateInventoryLotData" ) );
            for( int i=0; i < lineItemCount; i++ )
            {
                if( ( ( CustomerShippingLineItems ) lineItems.get(i) ).getQtyPulled() > 0 )
                {
                    if( finalInventoryMap != null )
                    {
                        int csLineItemId = ( ( CustomerShippingLineItems ) lineItems.get(i) ).getCustomerShippingLineItemId();

                        finalInventoryDataList = ( ArrayList ) finalInventoryMap.get ( ( ( CustomerShippingLineItems ) lineItems.get(i) ).getLineItemId()+"" );
                        if( finalInventoryDataList != null )
                        {
                            finalInventoryDataListSize = finalInventoryDataList.size();

                            for( int j = 0; j < finalInventoryDataListSize; j++ )
                            {
                                inventoryData = (FinalInventoryData) finalInventoryDataList.get ( j ) ;
                                pstmt.setInt( 1, inventoryData.getLotId() );
                                pstmt.setInt( 2, inventoryData.getBalance() );
                                pstmt.setInt( 3, inventoryData.getCsLineItemId() );
                                pstmt.setInt( 4, inventoryData.getQuantityPull() );
                                pstmt.setInt( 5, inventoryData.getIsReduceBalance() );
                                pstmt.setInt( 6, 1 );
                                pstmt.setString( 7, packingSlipForm.getShipDate() );
                                pstmt.setInt   ( 8, 1); // '0' is set when vendor is updating then Inventory else set '1'.
                                pstmt.setString ( 9, inventoryData.getDateCode() ); // '0' is set when vendor is updating then Inventory else set '1'.
                                //System.out.println("------------PS DAO SAVE--inventoryData " + inventoryData);
                                pstmt.addBatch();
                            }
                            pstmt.executeBatch();
                            pstmt.clearBatch();
                        }
                    }
                }
            }

            pstmt.close();
            pstmt = null;

            // Step 8
            if( !confirmPackingSlip )
            {
                String loadingPort =null;
                String countryOfOrigin = null;
                String licenseSymbol = null;
                String eccn = null;
                String scheduleNumber = null;

                // Step 1
                // We have ShippingTo Address, and get country and add to commercial invoice desitination port
                /*String destinationPort = null; //Commented by Krishna Prasad on 22 June 2004 , this code moved to Starting of Method because getting problem with transcation Obj in GP Integration.
                    if( packingSlipForm.getShippingToAddressId() != -1 )
                    {
                        Address shippingAddress = UserDAO.loadAddressById( packingSlipForm.getShippingToAddressId() );

                        if( shippingAddress.getCountry() != null && shippingAddress.getCountry().trim().length() != 0 )
                        {
                            destinationPort = shippingAddress.getCountry();
                        }
                    } */

                // retereving the Cleint Speicfic values of Commerical Invoice
                //pstmt = conn.prepareStatement ( "SELECT * FROM COMMERCIAL_INVOICE_TABLE WHERE CLIENT_ID = 1" );
                pstmt = conn.prepareStatement ( SqlQueryMappings.get( "operations.commercialinvoice.loadCommercialInvoiceInitialValues" ) );
                rset = pstmt.executeQuery();

                if ( rset.next() != false )
                {
                    loadingPort 	= rset.getString( "LOADING_PORT" );
                    countryOfOrigin = rset.getString( "COUNTRY_OF_ORIGIN" );
                    licenseSymbol  	= rset.getString( "LICENSE_SYMBOL" );
                    eccn 			= rset.getString( "ECCN" );
                    //scheduleNumber 	= rset.getString( "SCHEDULE_NUMBER" );   // Commented by Krishna 09 June 2004
                }
                rset.close();
                pstmt.close();
                pstmt = null;

                if ( loadingPort != null ) loadingPort = loadingPort.trim();
                if ( countryOfOrigin != null )countryOfOrigin = countryOfOrigin.trim();
                if ( licenseSymbol != null )licenseSymbol = licenseSymbol.trim();
                if ( eccn != null )eccn = eccn.trim();
                if ( scheduleNumber != null )scheduleNumber = scheduleNumber.trim();

                CommercialInvoiceForm commercialInvoiceForm = new CommercialInvoiceForm();
                commercialInvoiceForm.setPortOfLanding( loadingPort );
                commercialInvoiceForm.setCountryOfOrigin( countryOfOrigin );
                commercialInvoiceForm.setLicenseSymbol( licenseSymbol );

                if( destinationPort != null && destinationPort.trim().length() != 0	)
                {
                    commercialInvoiceForm.setPortOfDestination( destinationPort );
                }
                else
                {
                    commercialInvoiceForm.setPortOfDestination( null );
                }
                commercialInvoiceForm.setEccn( eccn );
                commercialInvoiceForm.setCustomerShippingId( customerShippingId );

                HashMap lineItemScheduleNumberMap = new HashMap();

                for ( int i = 0; i < lineItemCount; i++ )
                {
                    lineItem = ( CustomerShippingLineItems ) lineItems.get (i);

                    scheduleNumber = getScheduleNumberByProductId ( null , lineItem.getProduct_ID() );  // Added by Krishna 09 June 2004

                    if( lineItem.getQtyPulled() > 0 )
                    {
                        lineItemScheduleNumberMap.put( lineItem.getLineItemId()+"", scheduleNumber );
                    }
                }
                CommercialInvoiceDAO.saveCommercialInvoice( dbTransaction, commercialInvoiceForm, lineItemScheduleNumberMap );
            }
            // -- END --

            conn.close();
            conn = null;

            // Step 7
            // Update the Lot_Reservation_Table Release the lots reserved for the Packing slip
            for ( int i = 0; i < lineItemCount; i++ )
            {
                lineItem = ( CustomerShippingLineItems ) lineItems.get (i);
                if( lineItem.getQtyPulled() > 0 )
                {
                    releaseReservedLots ( dbTransaction , lineItem.getLineitemNumber()+""  , packingSlipForm.getSalesOrderId()+"" );
                }
            }
            success = true;
        }
        catch (Exception sqle) {
            throw new ScalarException ("Could not save customer shipping information into database", sqle);
        }
        finally
        {
            try {
                if(dbTransaction == null){
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                else
                {
                    try
                    {
                        if (success == true) {
                            dbTransaction.commit();
                        }else{
                        dbTransaction.rollback();
                        }
                    }
                    catch (DBXException e)
                    {
                        e.printStackTrace();
                    }

                }
            }
            catch (SQLException ignore) {}

            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

        return customerShippingId;
    }

    private static String getScheduleNumberByProductId ( DBTransaction dbTransaction , int productId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        String scheduleNumber = null;
        boolean success = false;
        String dbParameterKey = "";

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareCall ( SqlQueryMappings.get ( "operations.packingslip.loadProcessStepParametersByProductId" )  ); // Added by Krishna 28 June 2004, SqlQueryMappings
            pstmt.setInt ( 1 , productId );
            rset = pstmt.executeQuery();
            while ( rset.next() )
            {
                dbParameterKey = rset.getString ( "Parameter_Key" );
                if( dbParameterKey.equalsIgnoreCase ( "scheduleno" ) )
                {
                    scheduleNumber = rset.getString ( "Parameter_Value" );
                }
            }
            success = true;
        }
        catch (Exception e) {
            throw new ScalarException ("Could not retrieve PackingSlip list from database", e);
        }
        finally {
            if ( dbTransaction == null )
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
        return scheduleNumber;
    }

    // Added by Krishna 10 June 2004 , to check unique no from db
    public static int getUniqueRandomKey( DBTransaction dbTransaction ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        int randomKey = new Random(System.currentTimeMillis()).nextInt();
        int finalRandomKey = -1;

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            //pstmt = conn.prepareCall ("Select CustomerShipping_ID >From Customer_Shipping_Table Where CST_Random_Key = ? ");
            pstmt = conn.prepareCall ( SqlQueryMappings.get ( "operations.packingslip.getUniqueRandomKey" ) ); // Added by Krishna 28 June 2004, SqlQueryMappings
            pstmt.setInt (1, randomKey);
            rset = pstmt.executeQuery();
            if ( rset.next() == false)
            {
                finalRandomKey = randomKey;
            }
            else
            {
                finalRandomKey = -1;
            }
        }
        catch (Exception e) {
            throw new ScalarException ("Could not retrieve PackingSlip list from database", e);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
        return finalRandomKey;
    }

    public static int saveAddress(DBTransaction dbTransaction, int addressId,
                                  String address1, String address2, String address3, String address4, String city, String state, String zipCode,
                                  String country, String phone, String fax, String email, String contactPerson, String cellNumber, boolean insert, String customerNumber, String epicoreShipCode, String tagName) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;


        try
        {
            if ( dbTransaction == null ) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            if( insert ) {
                pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.customer.insertAddress" ) );
            }
            else {
                pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.customer.updateAddress" ) );
            }

            pstmt.setString (1, address1);
            pstmt.setString (2, address2);
            pstmt.setString (3, city);
            pstmt.setString (4, state);
            pstmt.setString (5, zipCode);
            pstmt.setString (6, country);
            pstmt.setString (7, phone);
            pstmt.setString (8, fax);
            pstmt.setString (9, email);
            pstmt.setString (10, contactPerson);
            pstmt.setString (11, cellNumber);
            pstmt.setString (12, address3);
            pstmt.setString (13, address4);
            pstmt.setString (14, epicoreShipCode);
            pstmt.setString (15, tagName);


            if( !insert ) {
                pstmt.setInt (16, addressId);
                pstmt.executeUpdate();
            }
            else {
                pstmt.execute();
                //pstmt = conn.prepareStatement("SELECT @@IDENTITY as address_id");
                pstmt = conn.prepareStatement( SqlQueryMappings.get ( "operations.packingslip.saveAddressInfo.readIdentity" ) ); // Added by Krishna 28 June 2004, SqlQueryMappings
                rs = pstmt.executeQuery();

                if ( rs.next() ) {
                    addressId = rs.getInt("address_id");
                }
            }

            success = true;
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not update Address into database", sqle);
        }
        finally
        {
            if ( dbTransaction == null )
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }

            if (rs != null) {
                try { rs.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
        return addressId;
    }


    /**
     * This Method is used to update the PS
     * Used in
     * 1. UpdatePackingSlipAction
     */

    public static int updateCustomerShippingInfo( PackingSlipForm packingSlipForm, HashMap finalInventoryMap, String currentUser ) throws ScalarException
    {
        Connection conn 			= null;
        PreparedStatement pstmt 	= null;
        //ResultSet rset 			= null;
        boolean success 			= false;
        int customerShippingId	 	= -1;
        DBTransaction dbTransaction = null;
        double dWeight              = 0.0;
        double grossWeight          = 0.0;
        //String packingSlipStatus 	=  CUSTOMER_SHIPPING_STATUS_ISSED ;

        try
        {
            HashMap InventoryDataMapDB = getFinalInventoryMapByLineItemId( NewPackingSlipDAO.loadLotNumberDataByCustomerShippingId ( packingSlipForm.getCustomerShippingId() ) );
            mergeDBInventoryDataMapIntoExistMap( finalInventoryMap , InventoryDataMapDB );

            // Setting the LotIDs for the New Lots entered through Drop Shipment
            if( packingSlipForm.isDropShippment() && ! packingSlipForm.getStatus().equalsIgnoreCase( "shipped" ) )
            {
                    fetchLotIdForNewLots( dbTransaction, finalInventoryMap );
            }

            dbTransaction = DBTransaction.getInstance();
            if( dbTransaction != null )
            {
                dbTransaction.begin();
            }

            if( packingSlipForm.getNewShippingAddress() != null && packingSlipForm.getNewShippingAddress().getAddress1() != null && packingSlipForm.getNewShippingAddress().getAddress1().trim().length() > 0 )
            {

                Address shippingAddress =  packingSlipForm.getNewShippingAddress();   //Shipping Address Object
                saveAddress( 	dbTransaction,
                                packingSlipForm.getShippingToAddressId() ,
                                shippingAddress.getAddress1(),
                                shippingAddress.getAddress2(),
                                shippingAddress.getAddress3(),
                                shippingAddress.getAddress4(),
                                shippingAddress.getCity(),
                                shippingAddress.getState(),
                                shippingAddress.getZipCode(),
                                shippingAddress.getCountry(),
                                shippingAddress.getPhoneNumber(),
                                shippingAddress.getFaxNumber(),
                                shippingAddress.getEmail(),
                                shippingAddress.getContactPerson(),
                                shippingAddress.getCellNumber(),
                                false, packingSlipForm.getCustomerNumber()  , null, shippingAddress.getTagName());
            }
            if (packingSlipForm.getNewBillingAddressId() > 0 && packingSlipForm.getBillingAddressObject().getAddress1() != null && packingSlipForm.getBillingAddressObject().getAddress1().trim().length() > 0) {

                Address billingAddress = packingSlipForm.getBillingAddressObject();
                saveAddress(dbTransaction,
                        packingSlipForm.getBillingAddressId(),
                        billingAddress.getAddress1(),
                        billingAddress.getAddress2(),
                        billingAddress.getAddress3(),
                        billingAddress.getAddress4(),
                        billingAddress.getCity(),
                        billingAddress.getState(),
                        billingAddress.getZipCode(),
                        billingAddress.getCountry(),
                        billingAddress.getPhoneNumber(),
                        billingAddress.getFaxNumber(),
                        billingAddress.getEmail(),
                        billingAddress.getContactPerson(),
                        billingAddress.getCellNumber(),
                        false, packingSlipForm.getCustomerNumber(), null, billingAddress.getTagName());
            }

            if( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            try
            {
                dWeight = Double.parseDouble( packingSlipForm.getWeight() );
            }
            catch( Exception e ) { dWeight = 0.0; }
            try
            {
                grossWeight = Double.parseDouble( packingSlipForm.getGrossWeight() );
            }
            catch( Exception e ) { grossWeight = 0.0; }

            if( packingSlipForm.getEtd() != null && packingSlipForm.getEtd().trim().length() == 0 )
            {
                packingSlipForm.setEtd( null );
            }
            if( packingSlipForm.getEta() != null && packingSlipForm.getEta().trim().length() == 0 )
            {
                packingSlipForm.setEta( null );
            }

            // Step 1
            // Update Customer Shipping Info into Customer Shipping Table
            // pstmt = conn.prepareStatement ( "UPDATE Customer_Shipping_Table SET CST_Shipping_Contact_Person = ?, CST_Box_Count = ?, CST_AW_Bill_Number = ?, CST_Ship_Date = ?, CST_Weight = ?, Inv_No = ?, CST_Updated_By = ?, CST_Updated_On = CURRENT_TIMESTAMP, CST_Method = ? WHERE CustomerShipping_ID = ?" );
            pstmt = conn.prepareStatement ( SqlQueryMappings.get( "operations.packingslip.updateCustomerShippingInfo" ) );

            pstmt.setString ( 1, packingSlipForm.getContactPerson() );
            pstmt.setInt 	( 2, packingSlipForm.getNoOfBoxesAsInt() );
            pstmt.setString ( 3, packingSlipForm.getTrackingAsString() );
            //pstmt.setString ( 4, packingSlipForm.getShipDate() );
            pstmt.setDate   ( 4, StringUtils.getSQLDate( packingSlipForm.getShipDate(), null ) );
            pstmt.setDouble ( 5, dWeight);
            pstmt.setString ( 6, packingSlipForm.getInvNo() );
            pstmt.setString ( 7, currentUser );
            pstmt.setString ( 8, packingSlipForm.getShippedVia() );
            pstmt.setString ( 9, packingSlipForm.getShippingInstructions() );
            pstmt.setString ( 10, packingSlipForm.getComments() );
            pstmt.setString ( 11, packingSlipForm.getFreightInstructions() );
            pstmt.setDouble ( 12, grossWeight );
            pstmt.setString ( 13, packingSlipForm.getPackageDimension() );
            pstmt.setString ( 14, packingSlipForm.getWeightType() );
            pstmt.setString ( 15, packingSlipForm.getUomPkgDimension() );
            pstmt.setString ( 16, packingSlipForm.getMawb() );
            pstmt.setString ( 17, packingSlipForm.getHawb() );
            pstmt.setString ( 18, packingSlipForm.getFlightNumberOne() );
            pstmt.setString ( 19, packingSlipForm.getFlightNumberTwo() );
            pstmt.setString ( 20, packingSlipForm.getEtd() );
            pstmt.setString ( 21, packingSlipForm.getEta() );
            
            pstmt.setInt 	( 22, packingSlipForm.getCustomerShippingId() );

            pstmt.executeUpdate();
            pstmt.close();
            pstmt = null;

             // Step 2
            // Update into Customer_shipping_line_Item table
            pstmt = conn.prepareStatement (  SqlQueryMappings.get( "operations.packingslip.updateLineItems" ) );

            CustomerShippingLineItems lineItem = null;
            int lineItemCount 	= 0;
            String shippedPartNumbers = null;

            ArrayList lineItems = packingSlipForm.getLineitems();
            if( lineItems != null && lineItems.isEmpty() == false )
            {
                lineItemCount = lineItems.size();
            }

            for ( int i = 0; i < lineItemCount; i++ )
            {
                lineItem = ( CustomerShippingLineItems ) lineItems.get (i);
                if( lineItem.getQtyPulled() > 0 )
                {
                    pstmt.setInt 	( 1, lineItem.getQtyPulled() );
                    pstmt.setString ( 2, packingSlipForm.getShipDate() );
                    pstmt.setString ( 3, lineItem.getCool_Description() );
                    pstmt.setString ( 4, lineItem.getUnits() );
                    pstmt.setString ( 5, lineItem.getCustomer_Part_Number() );
                    shippedPartNumbers = getShippedPartNumberString( lineItem.getShipped_Part_Number(), null );
                    if( shippedPartNumbers != null && shippedPartNumbers.trim().length() > 0 && ! shippedPartNumbers.equals( lineItem.getPart_Number() ) )
                    {
                        pstmt.setString	( 6, shippedPartNumbers );
                    }
                    else
                    {
                        pstmt.setString	( 6, null  );
                    }
                    shippedPartNumbers = null;
                    pstmt.setString ( 7, lineItem.getCustomer_Label_Number() );//Added by Krishna Prasad on 23-11-2004
                    pstmt.setInt 	( 8, lineItem.getCustomerShippingLineItemId());

                    pstmt.addBatch();
                }
            }
            pstmt.executeBatch();
            pstmt.close();
            pstmt = null;

            // Step 3
            // Update 'Balance' and 'Shipping' Fields in Inventory_Lot Table

            int finalInventoryDataListSize = -1;
            ArrayList finalInventoryDataList = null;
            FinalInventoryData inventoryData = null;

            if( finalInventoryMap != null )
            {
                //pstmt = conn.prepareStatement ( "{call sp_update_balance_qty_in_inventory_lot (?, ?, ?, ?) }" );
                pstmt = conn.prepareStatement ( SqlQueryMappings.get( "operations.packingslip.updateInventoryLotData" ) );

                for( int i = 0; i < lineItemCount; i++ )
                {
                    finalInventoryDataList = ( ArrayList ) finalInventoryMap.get ( ( ( CustomerShippingLineItems ) lineItems.get(i) ).getLineItemId()+"" );
                    if( finalInventoryDataList != null )
                    {
                        finalInventoryDataListSize = finalInventoryDataList.size();
                        for( int j = 0; j < finalInventoryDataListSize; j++ )
                        {
                            inventoryData = (FinalInventoryData) finalInventoryDataList.get ( j ) ;
                            pstmt.setInt( 1, inventoryData.getLotId() );
                            pstmt.setInt( 2, inventoryData.getBalance() );
                            pstmt.setInt( 3, inventoryData.getCsLineItemId() );
                            pstmt.setInt( 4, inventoryData.getQuantityPull() );
                            pstmt.setInt( 5, inventoryData.getIsReduceBalance() );
                            pstmt.setInt( 6, 1 );
                            pstmt.setString( 7, packingSlipForm.getShipDate() );
                            pstmt.setInt   ( 8, 1); // '0' is set when vendor is updating then Inventory else set '1'.
                            pstmt.setString( 9, inventoryData.getDateCode() );
                            //System.out.println("------------PS DAO--UPDATE--inventoryData = " + inventoryData );
                            pstmt.addBatch();
                        }
                        pstmt.executeBatch();
                        pstmt.clearBatch();
                    }
                }
                pstmt.close();
                pstmt = null;
            }
            // Step 4
            // Update into Customer_Shiping_Lot_Number Table

            if( finalInventoryMap != null && finalInventoryMap.isEmpty() == false )
            {
                //pstmt = conn.prepareStatement( "{ call sp_save_customer_shipping_lot_number_data( ?, ?, ? ) }" );
                pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.packingslip.saveCustomerShippingLotNumberInfo" ) );
                for( int i=0; i < lineItemCount; i++ )
                {
                    finalInventoryDataList = ( ArrayList ) finalInventoryMap.get ( ( ( CustomerShippingLineItems ) lineItems.get(i) ).getLineItemId()+"" );

                    if( finalInventoryDataList != null )
                    {
                        finalInventoryDataListSize = finalInventoryDataList.size();

                        for( int j = 0; j < finalInventoryDataListSize; j++ )
                        {
                            inventoryData = (FinalInventoryData) finalInventoryDataList.get ( j ) ;
                            pstmt.setInt( 1, inventoryData.getCsLineItemId() );
                            pstmt.setInt( 2, inventoryData.getLotId() );
                            pstmt.setInt( 3, inventoryData.getQuantityPull() );
                            pstmt.addBatch();
                        }
                        pstmt.executeBatch();
                        pstmt.clearBatch();
                    }
                }
                pstmt.close();
                pstmt = null;
            }

            success = true;
        }
        catch (Exception sqle) {
            throw new ScalarException ("Could not Update customer shipping information into database", sqle);
        }
        finally
        {
            try {
                if(dbTransaction == null){
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                else
                {
                    try
                    {
                        if (success == true) {
                            dbTransaction.commit();
                        }else{
                        dbTransaction.rollback();
                        }
                    }
                    catch (DBXException e)
                    {
                        e.printStackTrace();
                    }

                }
            }
            catch (SQLException ignore) {}

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
        return customerShippingId;
    }

    /*
     * This method is used to get the Packingslip details by Customer Shipping Id
     * Used in
     * 1. ViewPackingSlipAction
     */

    public static PackingSlipForm loadPackingSlipById ( int customerShipingId ) throws ScalarException
    {

        Connection conn 				= null;
        PreparedStatement pstmt 		= null;
        ResultSet rset 					= null;
        String psContactPerson          = "" ;
        PackingSlipForm packingSlipForm = null;
        DBTransaction dbTransaction 	= null;
        String paletteCount             = null;
        String weigt                    = "";

        try
        {

            dbTransaction = DBTransaction.getInstance();

            if( dbTransaction != null )
            {
                dbTransaction.begin();
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            // Step 1: Load data from Customer_Shipping Table
            pstmt = conn.prepareCall ( SqlQueryMappings.get( "operations.packingslip.fetchCustomerShippingData" ) );
            pstmt.setInt ( 1, customerShipingId );
            rset = pstmt.executeQuery();

            if ( rset.next() )
            {
                packingSlipForm = new PackingSlipForm ();

                packingSlipForm.setLabel( rset.getString ( "Label" ) );
                packingSlipForm.setCustomerShippingId( customerShipingId );
                packingSlipForm.setSalesOrderId	( rset.getInt( "SalesOrder_ID" ) );
                psContactPerson = rset.getString ( "CST_Shipping_Contact_Person" );
                packingSlipForm.setFob( rset.getString ( "CST_FOB" ) );
                packingSlipForm.setNoOfBoxes( rset.getInt ( "CST_Box_Count" )+"" );
                String[] tracking =  StringUtils.toStringArray( rset.getString ( "CST_AW_Bill_Number" ), ";" );
                if ( tracking == null ||  tracking.length == 0)
                {
                    tracking = new String[]{""};
                }
                packingSlipForm.setTracking( tracking );
                packingSlipForm.setShippingInstructions	( rset.getString ( "CST_Shipping_Instructions" ) );	packingSlipForm.setShippingToAddressId 	( rset.getInt ( "CST_TO_Address_ID" ) );
                packingSlipForm.setShippingFromAddressId( rset.getInt ( "CST_From_Address_ID" ) );
                packingSlipForm.setStatus 	( rset.getString ( "CST_Status" ) );
                //packingSlipForm.setShipDate ( rset.getString ( "CST_Ship_Date" ) );
                packingSlipForm.setShipDate ( StringUtils.formatDateToString( rset.getDate ( "CST_Ship_Date" ) ) );
                packingSlipForm.setWeight ( rset.getString( "CST_Weight" ) );
                packingSlipForm.setInvNo 	( rset.getString ( "Inv_No" ) );
                packingSlipForm.setShippedVia 	( rset.getString ( "CST_Method" ) );
                packingSlipForm.setDropShippment( !rset.getBoolean( "CST_IS_Drop_Shipment" ) );
                packingSlipForm.setComments( rset.getString ( "CST_comments" ) );
                packingSlipForm.setBillingAddressId(rset.getInt ( "CST_Billing_Address_Id" ) );
                packingSlipForm.setShippingFromLocationId( rset.getInt ( "CST_Location_Address_Id") );
                packingSlipForm.setCstRandomKey( rset.getInt ( "CST_Random_Key" ) );
                paletteCount = rset.getString( "CST_PALETTE_COUNT" );
                packingSlipForm.setPackedby( rset.getString ( "CST_Packedby" ) );
                packingSlipForm.setTitle( rset.getString ( "CST_Title" ) );
                packingSlipForm.setRepOffice( rset.getString ( "CST_Regional_Rep" ) );
                packingSlipForm.setSupplierNumber( rset.getString ( "CST_Supplier_Number" ) );
                packingSlipForm.setOdmSupplierNumber( rset.getString ( "CST_ODM_Supplier_Number" ) );
                packingSlipForm.setOdmId( rset.getInt ( "CST_ODM_ID" ) );
                packingSlipForm.setFreightInstructions ( rset.getString ( "CST_Freight_Instructions" ) );
                if( "0".equalsIgnoreCase( paletteCount ) )
                {
                    packingSlipForm.setPaletteCount( "" );
                }
                else
                {
                    packingSlipForm.setPaletteCount( paletteCount ); 
                }
                packingSlipForm.setGrossWeight( rset.getString( "CST_Gross_Weight" ) );
                packingSlipForm.setPackageDimension( rset.getString( "CST_Package_Dimension" ) );
                packingSlipForm.setWeightType( rset.getString( "CST_UOM_Weight" ) );
                packingSlipForm.setUomPkgDimension ( rset.getString( "CST_UOM_Pkg_Dimension" ) );
                packingSlipForm.setMawb( rset.getString( "CST_MAWB" ) );
                packingSlipForm.setHawb( rset.getString( "CST_HAWB" ) );
                packingSlipForm.setFlightNumberOne( rset.getString( "CST_FLIGHT_NUMBER" ) );
                packingSlipForm.setFlightNumberTwo( rset.getString( "CST_FLIGHT_NUMBER_TWO" ) );
                packingSlipForm.setEtd( StringUtils.formatDateToString( rset.getDate ( "CST_ETD" ) ) );
                packingSlipForm.setEta( StringUtils.formatDateToString( rset.getDate ( "CST_ETA" ) ) );
            }

            rset.close();
            rset = null;

            pstmt.close();
            pstmt = null;
            // Closing the Connection here such that the two methods bellow
            // This can open a new connection
            conn.close();
            conn = null;

            SalesOrder salesOrder 	= SalesOrderDAO.loadSalesOrder ( packingSlipForm.getSalesOrderId()+"", dbTransaction );
            Customer customer 		= CustomerDAO.loadCustomerById ( salesOrder.getCustomerId() );

            packingSlipForm.setSalesOrderNumber( salesOrder.getSalesOrderNo() );
            packingSlipForm.setCustomerPO( salesOrder.getCustomerPO() );
            packingSlipForm.setCustomerName( salesOrder.getCustomerName() );
            packingSlipForm.setCustomerNumber( customer.getCustomerNumber() );
            /*packingSlipForm.setRepOffice( customer.getRepOffice() );*/
            packingSlipForm.setTerms( salesOrder.getDeliveryTerms() );
            //packingSlipForm.setShippedVia( salesOrder.getShippingMethod() );
            packingSlipForm.setShippingMethod( salesOrder.getShippingMethod() );
            packingSlipForm.setShippingAccount( salesOrder.getShippingAccount() );
            packingSlipForm.setLetterOfCreditNo( salesOrder.getLetterOfCreditNo() );
            packingSlipForm.setIsSample( salesOrder.getIsSample());
            packingSlipForm.setConsignment( "0".equals( salesOrder.getIsConsignmentSales() ) );

            if ( packingSlipForm.getOdmId() > 0 )
            {
                Customer odmCustomer = CustomerDAO.loadCustomerById ( packingSlipForm.getOdmId() );
                packingSlipForm.setOdm( odmCustomer.getCustomerName() );
            }

            if ( packingSlipForm.getBillingAddressId() == 0 )
            {
                //We are Assigning ShippingFromAddressId to  BillingAddressId because
                //new column called  CST_Billing_Address_Id is added to CST table on 26 May 2004
                //So Previously 'Billing Address' used to get from ShippingFromAddressId DB Field
                //But now we are getting from For CST_Billing_Address_Id DB Field, so prvious PS records we fixed like this
                //packingSlipForm.setBillingAddressId( packingSlipForm.getShippingFromAddressId() );
                packingSlipForm.setBillingAddressId( salesOrder.getBillingAddressId() );
            }
            packingSlipForm.setShippingVendorAddressObject( VendorDAO.getVendorNameAndAddress(packingSlipForm.getShippingFromLocationId()));
            packingSlipForm.setBillingAddressObject ( CustomerDAO.getAddress ( packingSlipForm.getBillingAddressId() ) );
            packingSlipForm.setBillingAddress( packingSlipForm.getBillingAddressObject().getAddressAsString () );

            if ( packingSlipForm.getShippingToAddressId() > 0 )
            {
                packingSlipForm.setShippingAddressObject ( CustomerDAO.getAddress ( packingSlipForm.getShippingToAddressId() ) );
                packingSlipForm.setShippingAddress( packingSlipForm.getShippingAddressObject().getAddressAsString ( ) );
            }


            if ( packingSlipForm.getShippingFromLocationId() == 0  )
            {
                //We are Assigning ShippingFromLocationId to  ShippingFromLocationId because
                //new column called  CST_Location_Address_Id is added to CST table on 26 May 2004
                //So Previously 'Location Id' used to get from ShippingFromAddressId DB Field
                //But now we are getting from For CST_Location_Address_Id DB Field, so prvious PS records we fixed like this
                packingSlipForm.setShippingFromLocationId( packingSlipForm.getShippingFromAddressId() );
            }
            //To load the vendor Location Address as Shippers Address
            packingSlipForm.setShippersAddressObject( CustomerDAO.getAddress( packingSlipForm.getShippingFromAddressId() ) );
            int vendorLocationId =  packingSlipForm.getShippingFromLocationId();
            int formAddressId = packingSlipForm.getShippingFromAddressId() ;
            VendorAddress va = new VendorAddress();
            if ( vendorLocationId > 0)
            {
                va = VendorDAO.getVendorNameAndAddress( vendorLocationId );
            }
            else
            {
                //We are Assigning ShippingFromLocationId to  ShippingFromLocationId because
                //new column called  CST_Location_Address_Id is added to CST table on 26 May 2004
                //So Previously 'Location Id' used to get from ShippingFromAddressId DB Field
                //But now we are getting from For CST_Location_Address_Id DB Field, so prvious PS records we fixed like this
                va = VendorDAO.getVendorNameAndAddress( formAddressId );
            }
            packingSlipForm.setVendorName( va.getVendorName() );

            packingSlipForm.setContactPerson ( psContactPerson );

            if( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            pstmt = conn.prepareStatement ( SqlQueryMappings.get( "operations.packingslip.fetchCustomerShippingLineItemData" ) );

            pstmt.setInt( 1, packingSlipForm.getCustomerShippingId() );
            pstmt.setInt( 2, packingSlipForm.getSalesOrderId() );
            pstmt.setInt( 3, packingSlipForm.getShippingFromLocationId() );

            rset = pstmt.executeQuery();
            CustomerShippingLineItems cslt = null;

            ArrayList lineItemList = new ArrayList();
            String[] shippedPartNo = null;
            ArrayList subPartNoList = null;

            while(rset.next())
            {
                cslt = new CustomerShippingLineItems();
                cslt.setLineItemId	  ( rset.getInt( "lineItemId" ) );
                cslt.setCustomerShippingLineItemId( rset.getInt( "CSLIT_Id" ) );
                cslt.setQtyToShip	  ( rset.getInt( "qtyToShip" ) );
                cslt.setSalesOrder_ID ( rset.getInt( "SalesOrder_ID" ) );
                cslt.setLineitemNumber( rset.getInt( "COOL_Line_Item_No" ) );
                cslt.setProduct_ID	  ( rset.getInt( "Product_ID" ) );
                cslt.setCool_Qty	  ( rset.getInt( "COOL_Qty" ) );
                cslt.setCool_Unit_Cost( rset.getFloat( "COOL_Unit_Cost" ) );
                cslt.setCool_Amount	  ( rset.getFloat( "COOL_Amount" ) );
                cslt.setQtyPulled	  ( rset.getInt( "Qty_Pulled" ) );
                cslt.setOrginalQtyPulled( rset.getInt( "Original_Qty_Pulled" ) );

                cslt.setCool_Requested_Date	( StringUtils.formatDateToString( rset.getDate( "COOL_Requested_Date" ), null ) );
                cslt.setCool_Booking_Date	( StringUtils.formatDateToString( rset.getDate( "COOL_Booking_Date" ), null ) );
                cslt.setCool_Sugg_Schedule_Date_With_Substitutables( StringUtils.formatDateToString( rset.getDate( "COOL_Sugg_Schedule_Date_With_Substitutables" ), null ) );
                cslt.setCool_Sugg_Schedule_Date_Without_Substitutables( StringUtils.formatDateToString( rset.getDate( "COOL_Sugg_Schedule_Date_Without_Substitutables" ), null ) );
                cslt.setCool_Schedule_Date	( StringUtils.formatDateToString ( rset.getDate( "COOL_Schedule_Date" ), null ) );
                cslt.setCool_Revised_Date	( StringUtils.formatDateToString ( rset.getDate( "COOL_Revised_Date" ), null ) );
                cslt.setCool_Is_Cancel	( rset.getBoolean( "COOL_Is_Cancel" ) );
                cslt.setCool_Status		( rset.getString( "COOL_Status" ) );
                cslt.setCool_Taxable	( rset.getBoolean( "COOL_Taxable" ) );
                cslt.setCool_Description( rset.getString( "COOL_Description" ) );
                cslt.setCool_PO_Line_Item_No( rset.getInt( "COOL_PO_Line_Item_No" ) );
                cslt.setPart_Number		( rset.getString( "Part_Number" ) );
                cslt.setMkt_Part_Number	( rset.getString( "Mkt_Part_Number" ) );
                cslt.setCustomer_Part_Number( rset.getString( "Customer_Part_Number" ) );
                cslt.setQty_Available 	( rset.getInt( "Qty_Available" ) );
                cslt.setScheduleNumber	( rset.getString( "ScheduleNumber" ) );
                cslt.setSoftwareVersion	( rset.getString( "Software_version" ) );
                cslt.setUnits			( rset.getString( "Units" ) );
                cslt.setDropShippment   ( packingSlipForm.isDropShippment() );
                //cslt.setShipped_Part_Number ( getShippedPartNumbersByCsLineItemId  ( null , cslt.getCustomerShippingLineItemId(), cslt.getPart_Number(), "View" ) );
                //cslt.setSubstitutablePartNumbers ( loadShippedPartNumbersByCsLineItemId  ( null , cslt.getCustomerShippingLineItemId(), cslt.getPart_Number(), "View" ) );
                subPartNoList = loadShippedPartNumbersByCsLineItemId ( null , cslt.getCustomerShippingLineItemId(), cslt.getPart_Number(), "View" ) ;
                shippedPartNo = new String[ subPartNoList.size() ];
                subPartNoList.toArray( shippedPartNo );

                cslt.setShipped_Part_Number ( shippedPartNo );
                cslt.setSubstitutablePartNumbers ( loadSubstitutablePartNumbersByCsLineItemId  ( null , cslt.getCustomerShippingLineItemId(), cslt.getPart_Number(), "View", cslt.getProduct_ID() ) );
                cslt.setQtyPulledSubstitutablePartNo( getShippedPartNumbersByCsLineItemId  ( null , cslt.getCustomerShippingLineItemId(), cslt.getPart_Number(), "View" ) );

                cslt.setParentPartNumber( rset.getString( "Parent_Part_Number" ) );
                cslt.setCustomer_Label_Number( rset.getString( "Customer_Label_Number" ) );
                cslt.setOdm_Part_Number( rset.getString( "ODM_Part_Number" ) );
                lineItemList.add( cslt );
            }

            packingSlipForm.setLineitems( lineItemList );

            rset.close();
            rset = null;

            pstmt.close();
            pstmt = null;

            // Load the Client Information
            ClientInfo clientInfo = new ClientInfo();
            clientInfo = UserDAO.getClientAddressInfo(  );
            packingSlipForm.setClientInfo( clientInfo );

            // Load the Shipping Address
            //packingSlipForm.setShippingAddressObject( UserDAO.loadAddressById ( packingSlipForm.getShippingToAddressId() ) );

            //packingSlipForm.setCountryOfFabOrigin ( CommercialInvoiceDAO.getFabAssmCountryOfOrigin( customerShipingId, "FAB" ));
            //packingSlipForm.setCountryOfAssyOrigin( CommercialInvoiceDAO.getFabAssmCountryOfOrigin( customerShipingId, "ASSM" ) );

            packingSlipForm.setCountryOfFabOrigin( CommercialInvoiceDAO.loadCountryOfOrigin( null, customerShipingId, Constants.LOCATION_TYPE_FAB ) );
            packingSlipForm.setCountryOfAssyOrigin( CommercialInvoiceDAO.loadCountryOfOrigin( null, customerShipingId, Constants.LOCATION_TYPE_ASSEMBLY ) );

        }
        catch (Exception e) {
            e.printStackTrace();
            throw new ScalarException ("Could not retrieve PackingSlip from database", e);
        }
        finally {
            if( dbTransaction != null )	{
                dbTransaction.commit();
            }
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return packingSlipForm;
    }

    /* This method is used to fetch all the PackingSlips for the sales order under that Particular
      * Status of the Packing Slip
      * This method is used at
      * 1. GeneratePackingSlipAction
      * 2. ViewPackingSlipAction
      */

    public static ArrayList loadPackingSlipsBySalesOrderId ( int salesOrderId, String status ) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList packingSlipList = new ArrayList();

        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareCall (SqlQueryMappings.get ("operations.packingslip.loadPackingSlipsBySalesOrderId"));
            pstmt.setInt (1, salesOrderId);
            pstmt.setString (2, status);
            rset = pstmt.executeQuery();

            while (rset.next())
            {
                LabelValueBean valueBean = new LabelValueBean( rset.getString ("Label"), rset.getInt ("CustomerShipping_ID")+"" );
                packingSlipList.add ( valueBean );
            }
        }
        catch (Exception e) {
            throw new ScalarException ("Could not retrieve PackingSlip list from database", e);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return packingSlipList;
    }


    //Added by Krishna Prasad on 10 June 2004
    public static void releaseReservedLots (DBTransaction dbTransaction, String lineItemNumberStr, String salesOrderIdStr, int pulledQty) throws ScalarException {

         Connection conn = null;
         PreparedStatement pstmt = null;
         boolean success = false;

         try {

             int lineItemNumber = Integer.parseInt (lineItemNumberStr);
             int salesOrderId = Integer.parseInt (salesOrderIdStr);

             if(dbTransaction == null) {
                 conn = DataSource.getConnection();
             }
             else {
                 conn = dbTransaction.getConnection();
             }

             //pstmt = conn.prepareStatement( "update Lot_Reservation_Table set LRT_Released = 0, LRT_Released_By = 'SYSTEM', LRT_Released_On = getDate() Where LRT_Line_Item_No = ? and LRT_SalesOrder_ID = ?" );
             pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.packingslip.confirm.releaseLotreservationData" ) );
             pstmt.setInt ( 1 , lineItemNumber );
             pstmt.setInt ( 2 , salesOrderId );
             pstmt.setInt ( 3 , pulledQty );

             pstmt.execute();

             success = true;
         }

         catch (Exception sqle) {
             sqle.printStackTrace();
             throw new ScalarException ("Could not release reserved Lots for lineItem# " + lineItemNumberStr + ", salesOrder ID: " + salesOrderIdStr, sqle);
         }

         finally {

             if (dbTransaction == null) {

                 try {
                     if( success == true )
                     {
                        if (conn != null) {
                            conn.commit();
                        }
                     }
                     else
                     {
                        if (conn != null) {
                            conn.rollback();
                        }
                     }
                 }
                 catch (SQLException ignore) {}
             }

             if (pstmt != null) {
                 try { pstmt.close(); } catch (SQLException ignore) {}
             }

             if (conn != null) {
                 try { conn.close(); } catch (SQLException ignore) {}
             }
         }
     }


    /* This method is used to release the lots for this Lineitem
      * used in
      * 1. saveCustomerShippingInfo method in the NewPackingSlipDAO
      */
    public static void releaseReservedLots (DBTransaction dbTransaction, String lineItemNumberStr, String salesOrderIdStr) throws ScalarException {

         Connection conn = null;
         PreparedStatement pstmt = null;
         boolean success = false;

         try {

             int lineItemNumber = Integer.parseInt (lineItemNumberStr);
             int salesOrderId = Integer.parseInt (salesOrderIdStr);

             if(dbTransaction == null) {
                 conn = DataSource.getConnection();
             }
             else {
                 conn = dbTransaction.getConnection();
             }

             //pstmt = conn.prepareStatement( "update Lot_Reservation_Table set LRT_Released = 0, LRT_Released_By = 'SYSTEM', LRT_Released_On = getDate() Where LRT_Line_Item_No = ? and LRT_SalesOrder_ID = ?" );
             pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.packingslip.releaseLotreservationData" ) );
             pstmt.setInt (1, lineItemNumber);
             pstmt.setInt (2, salesOrderId);

             pstmt.execute();

             success = true;
         }

         catch (Exception sqle) {
             sqle.printStackTrace();
             throw new ScalarException ("Could not release reserved Lots for lineItem# " + lineItemNumberStr + ", salesOrder ID: " + salesOrderIdStr, sqle);
         }

         finally {

             if (dbTransaction == null) {
                 try {
                     if( success == true )
                     {
                        if (conn != null) {
                            conn.commit();
                        }
                     }
                     else
                     {
                        if (conn != null) {
                            conn.rollback();
                        }
                     }
                 }
                 catch (SQLException ignore) {}
             }

             if (pstmt != null) {
                 try { pstmt.close(); } catch (SQLException ignore) {}
             }

             if (conn != null) {
                 try { conn.close(); } catch (SQLException ignore) {}
             }
         }
     }

     /*	This method is usefull to fetch the Repeting Partnumbers based on the product
       * This method is Used in
       * 1. GeneratePackingSlipAction
       */
     public static ArrayList getRepetedPartNumbers( ArrayList orderLineItemList ) throws ScalarException
     {
         ArrayList repetedPartNumber = new ArrayList();
         ArrayList lineItemList = new ArrayList();
         HashMap productIdLineItemIdMap = new HashMap();

         Connection conn = null;
         PreparedStatement pstmt = null;
         ResultSet rset = null;
         String lineItemString = "";
         String productType = "";

         String productIdString = "";

         if( orderLineItemList.size() > 1)
         {
             try
             {
                 for( int i=0 ; i < orderLineItemList.size() ; i++ )
                 {
                     CustomerShippingLineItems lineItem = ( CustomerShippingLineItems )orderLineItemList.get(i);
                     lineItemString = lineItemString + lineItem.getLineItemId() + "," ;
                 }

                 if( lineItemString.length() > 1 )
                 {
                     lineItemString = lineItemString.substring( 0 , lineItemString.length() - 1 );
                 }

                 conn = DataSource.getConnection();
                 pstmt = conn.prepareStatement ("select LineItem_ID, cool.Product_ID, P_Parent_Product_ID, ISNULL( P_type, 'CHIP') P_type from product ,Customer_Open_Order_LineItem cool where cool.Product_ID = Product.Product_ID and LineItem_ID in ( "+ lineItemString + ")");
                 rset = pstmt.executeQuery();
                 /*	The Logic below is for Board and kit we can have child boards for
                      * which we pull the Quantity from Parent Board Inventory itself.
                      * And both Parent Board and Child Board will be present in the SalesOrderScreen
                      */
                 while (rset.next())
                 {
                     productType = rset.getString( "P_type" );
                     LineItem li = new LineItem();

                     if( productType.equalsIgnoreCase("CHIP") )
                     {
                         li.setProductId( rset.getInt( "Product_ID" ) );
                         li.setLineItemId( rset.getInt( "LineItem_ID" ) );
                     }
                     else
                     {
                         if( rset.getInt( "P_Parent_Product_ID" ) == -1 )
                             li.setProductId( rset.getInt( "Product_ID" ) );
                         else
                             li.setProductId( rset.getInt( "P_Parent_Product_ID" ) );

                         li.setLineItemId( rset.getInt( "LineItem_ID" ) );
                     }
                     lineItemList.add( li );
                 }

                 for( int i=0 ; i < lineItemList.size()-1 ; i++ )
                 {
                     LineItem lineItem = (LineItem) lineItemList.get(i);
                     int productID = lineItem.getProductId();
                     for(int j=i+1 ; j < lineItemList.size(); j++)
                     {
                         LineItem secLineItem = (LineItem) lineItemList.get(j);

                         int secProductID = secLineItem.getProductId();
                         if( productID == secProductID )
                         {
                             if( !repetedPartNumber.contains( lineItem.getLineItemId()+"" ) )
                                 repetedPartNumber.add( lineItem.getLineItemId()+"" );
                             if( !repetedPartNumber.contains( secLineItem.getLineItemId()+"" ) )
                                 repetedPartNumber.add( secLineItem.getLineItemId()+"" );
                         }
                     }
                 }

                 rset.close();
                 rset = null;
                 pstmt.close();
                 pstmt = null;

                 for( int i = 0 ; i < lineItemList.size() ; i++ )
                 {
                     LineItem lineItem = ( LineItem ) lineItemList.get(i);
                     productIdLineItemIdMap.put( lineItem.getProductId() + "" , lineItem.getLineItemId() + "" );
                     productIdString = productIdString + lineItem.getProductId() + "," ;
                 }
                 if( productIdString.length() > 1 )
                 {
                     productIdString = productIdString.substring( 0 , productIdString.length() - 1 );
                 }

                 int productId1= 0;
                 int productId2= 0;
                 //String lineItem1= "";
                 //String lineItem2= "";
                 pstmt = conn.prepareStatement ("SELECT P.Product_ID, P.Part_Number, P.ProcessStep_ID ,( select product_Id from product where ProcessStep_ID =  PST.Product_ProcessStep_Id ) Product_ID2 FROM Product P INNER JOIN Product_Substitutables_Table PST ON P.ProcessStep_ID = PST.Substitute_Product_ProcessStep_Id WHERE PST.Product_ProcessStep_Id IN ( select ProcessStep_ID from product where product_Id IN ( "+ productIdString + ") )");
                 rset = pstmt.executeQuery();
                 while (rset.next())
                 {
                     //lineItem1= "";
                     //lineItem2= "";
                     productId1 = rset.getInt( "Product_ID" );
                     productId2 = rset.getInt( "Product_ID2" );

                     if( productIdLineItemIdMap != null && productIdLineItemIdMap.isEmpty() == false )
                     {
                            Iterator it = productIdLineItemIdMap.keySet().iterator();
                            while ( it.hasNext() )
                            {
                                String pId = ( String )  it.next();
                                String lineItId = (String) productIdLineItemIdMap.get( pId );

                                if( Integer.parseInt(  pId ) == productId2 )
                                {
                                     if( !repetedPartNumber.contains( lineItId ) )
                                     {
                                         repetedPartNumber.add( lineItId ) ;
                                     }
                                }
                                if( Integer.parseInt(  pId ) == productId1 )
                                {
                                     if( !repetedPartNumber.contains( lineItId ) )
                                     {
                                         repetedPartNumber.add( lineItId ) ;
                                     }
                                }

                            }
                     }
                 }
             }
             catch (Exception e)
             {
                 e.printStackTrace();
                 throw new ScalarException ("Could not check for the repeted Part Numbers" , null);
             }
             finally {
                 if (rset != null) {
                     try { rset.close(); } catch (SQLException ignore) {}
                 }
                 if (pstmt != null) {
                     try { pstmt.close(); } catch (SQLException ignore) {}
                 }
                 if (conn != null) {
                     try { conn.close(); } catch (SQLException ignore) {}
                 }
             }
         }
         return repetedPartNumber;
     }


    /*  To Check wethere the Packing Slip Label Exists
      *  Used in
      *  1. SavePackingSlipAction
      */

    public static boolean doesPackingSlipLabelExist ( String packingSlipLabel ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        boolean success = false;

        if ( packingSlipLabel == null )
            return false;

        boolean labelExists = true;

        try
        {
            conn  = DataSource.getConnection();
            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "operations.packingslip.doesPackingSlipLabelExist" ) );
            pstmt.setString ( 1, packingSlipLabel );
            rset  = pstmt.executeQuery();

            if ( rset.next() )
            {
                labelExists = true;
            }
            else
            {
                labelExists = false;
            }

            success = true;
        }
        catch (Exception sqle)
        {
            throw new ScalarException ("Could not check whether " + packingSlipLabel + " exists or not", sqle);
        }
        finally
        {
            try {
                if (conn != null) {
                    if (success == true) {
                        conn.commit();
                    }
                    else {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {}

            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

        return labelExists;
    }

    /**
     * This Method updates the CST, CSLIT, CSLNT Table status fields to Cancelled
     * And replenish the Quantity Pulled back into Inventory Table
     * And set CST_CI_Show_Invoice = 1 to delete Commercial Invoice
     * used in
     * 1. CancelPackingSlipAction
     */
    public static void cancelPackingSlip( DBTransaction dbTransaction, int customerShippingId, String userId ) throws ScalarException
    {
        Connection conn = null;
         CallableStatement cstmt = null;
         boolean success = false;

        try
        {
             if(dbTransaction != null)
             {
                 conn = dbTransaction.getConnection();
             }
             else
             {                                                  
                 conn = DataSource.getConnection();
             }

             cstmt = conn.prepareCall ( SqlQueryMappings.get ( "operations.packingslip.cancelPackingSlip" ) );
            cstmt.setInt 	( 1, customerShippingId );
             cstmt.setString ( 2, CUSTOMER_SHIPPING_STATUS_CANCELLED );
            cstmt.setString ( 3, userId );
             cstmt.executeUpdate();

             success = true;
         }
         catch (Exception sqle) {
             throw new ScalarException ("Could not cancel packing", sqle);
         }
         finally
         {
             if (dbTransaction == null)
             {
                 try
                 {
                     if (conn != null)
                     {
                         if (success == true)
                         {
                             conn.commit();
                         }
                         else
                         {
                             conn.rollback();
                         }
                     }
                 }
                 catch (SQLException ignore) {}
             }

             if (cstmt != null)
             {
                 try { cstmt.close(); } catch (SQLException ignore){}
             }
             if (conn != null)
             {
                 try { conn.close(); } catch (SQLException ignore){}
             }
         }
    }

    /**
     *  This Method Updates the CST table, CSLIT table, CSLNT table
     *  set the status in Customer_Open_Order_LineItem to 'Shipped' or 'Partially Shipped'
     *  insert a record into Sales_Audit_Trail
     *  If the product is of BOARD or KIT then Update the Inventory_Lot update the Qty_In
     *  Used in
     *  1. ConfirmPackingSlipAction
     */
    public static void confirmPackingSlip( PackingSlipForm packingSlipForm, HashMap finalInventoryMap, String userId ) throws ScalarException
    {
		DBTransaction dbTransaction = null;
        Connection conn             = null;
        CallableStatement cstmt     = null;
        PreparedStatement pstmt     = null;
        boolean success             = false;
        int lineItemCount 	        = 0;
        String shippedPartNumbers   = null;
        CustomerShippingLineItems lineItem = null;
        double dWeight              = 0.0;
        double grossWeight          = 0.0;
        boolean isInvoiceNoExists   = true;
        String invoiceNo            = "";

        try
        {
			HashMap InventoryDataMapDB = getFinalInventoryMapByLineItemId( NewPackingSlipDAO.loadLotNumberDataByCustomerShippingId ( packingSlipForm.getCustomerShippingId() ) );

            mergeDBInventoryDataMapIntoExistMap( finalInventoryMap , InventoryDataMapDB );

            dbTransaction = DBTransaction.getInstance();

            if( dbTransaction != null )
            {
                dbTransaction.begin();
            }
            
            if ( packingSlipForm.getInvNo() == null || packingSlipForm.getInvNo().trim().length() == 0 )
            {
                invoiceNo = PackingSlipDAO.getNextPsInvoiceNumber( dbTransaction );
                while( isInvoiceNoExists )
                {
                    isInvoiceNoExists = isInvoiceNoExists( dbTransaction, invoiceNo );
                    if( isInvoiceNoExists )
                    {
                        invoiceNo = generateNextUniqueInvoiceNo( invoiceNo );
                    }
                }
                packingSlipForm.setInvNo( invoiceNo );
            }
            if( packingSlipForm.getNewShippingAddress() != null && packingSlipForm.getNewShippingAddress().getAddress1() != null && packingSlipForm.getNewShippingAddress().getAddress1().trim().length() > 0 )
            {
                Address shippingAddress =  packingSlipForm.getNewShippingAddress();   //Shipping Address Object
                saveAddress( 	dbTransaction,
                             packingSlipForm.getShippingToAddressId() ,
                             shippingAddress.getAddress1(),
                             shippingAddress.getAddress2(),
                             shippingAddress.getAddress3(),
                             shippingAddress.getAddress4(),
                             shippingAddress.getCity(),
                             shippingAddress.getState(),
                             shippingAddress.getZipCode(),
                             shippingAddress.getCountry(),
                             shippingAddress.getPhoneNumber(),
                             shippingAddress.getFaxNumber(),
                             shippingAddress.getEmail(),
                             shippingAddress.getContactPerson(),
                             shippingAddress.getCellNumber(),
                             false, packingSlipForm.getCustomerNumber()  , null, shippingAddress.getTagName());
            }

            if( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            // Setting the LotIDs for the New Lots entered through Drop Shipment
            if( packingSlipForm.isDropShippment() )
            {
                fetchLotIdForNewLots( dbTransaction, finalInventoryMap );
            }

            // Step 1:Update into Customer_shipping_line_Item table
            pstmt = conn.prepareStatement (  SqlQueryMappings.get( "operations.packingslip.updateLineItems" ) );
            ArrayList lineItems = packingSlipForm.getLineitems();
            if( lineItems != null && lineItems.isEmpty() == false )
            {
                lineItemCount = lineItems.size();
            }

            for ( int i = 0; i < lineItemCount; i++ )
            {
                lineItem = ( CustomerShippingLineItems ) lineItems.get (i);
                if( lineItem.getQtyPulled() > 0 )
                {
                    pstmt.setInt 	( 1, lineItem.getQtyPulled() );
                    pstmt.setString ( 2, packingSlipForm.getShipDate() );
                    pstmt.setString ( 3, lineItem.getCool_Description() );
                    pstmt.setString ( 4, lineItem.getUnits() );
                    pstmt.setString ( 5, lineItem.getCustomer_Part_Number() );

                    shippedPartNumbers = getShippedPartNumberString( lineItem.getShipped_Part_Number(), null );
                    if( shippedPartNumbers != null && shippedPartNumbers.trim().length() > 0 && ! shippedPartNumbers.equals( lineItem.getPart_Number() ) )
                    {
                        pstmt.setString	( 6, shippedPartNumbers );
                    }
                    else
                    {
                        pstmt.setString	( 6, null  );
                    }
                    //pstmt.setString	( 6, lineItem.getShipped_Part_Number() );
                    pstmt.setString ( 7, lineItem.getCustomer_Label_Number() );
                    pstmt.setInt 	( 8, lineItem.getCustomerShippingLineItemId());

                    pstmt.addBatch();
                }
            }
            pstmt.executeBatch();
            pstmt.close();
            pstmt = null;

            // Step 2: Update 'Balance' and 'Shipping' Fields in Inventory_Lot Table
            int finalInventoryDataListSize      = -1;
            ArrayList finalInventoryDataList    = null;
            FinalInventoryData inventoryData    = null;

            if( finalInventoryMap != null )
            {
                pstmt = conn.prepareStatement ( SqlQueryMappings.get( "operations.packingslip.updateInventoryLotData" ) );

                for( int i=0; i < lineItemCount; i++ )
                {
                    finalInventoryDataList = ( ArrayList ) finalInventoryMap.get ( ( ( CustomerShippingLineItems ) lineItems.get(i) ).getLineItemId()+"" );
                    if( finalInventoryDataList != null )
                    {
                        finalInventoryDataListSize = finalInventoryDataList.size();
                        for( int j = 0; j < finalInventoryDataListSize; j++ )
                        {
                            inventoryData = (FinalInventoryData) finalInventoryDataList.get ( j );
                            pstmt.setInt( 1, inventoryData.getLotId() );
                            pstmt.setInt( 2, inventoryData.getBalance() );
                            pstmt.setInt( 3, inventoryData.getCsLineItemId() );
                            pstmt.setInt( 4, inventoryData.getQuantityPull() );
                            pstmt.setInt( 5, inventoryData.getIsReduceBalance() );
                            pstmt.setInt( 6, 0 );
                            pstmt.setString( 7, packingSlipForm.getShipDate() );
                            pstmt.setInt   ( 8, 1); // '0' is set when vendor is updating then Inventory else set '1'.
                            pstmt.setString( 9, inventoryData.getDateCode() );
                            //System.out.println("------------PS DAO--CONFIRM--inventoryData--" + inventoryData);
                            pstmt.addBatch();
                        }
                        pstmt.executeBatch();
                        pstmt.clearBatch();
                    }
                }
                pstmt.close();
                pstmt = null;
            }
            // Step 4:Update into Customer_Shiping_Lot_Number Table
            if( finalInventoryMap != null && finalInventoryMap.isEmpty() == false )
            {
                pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.packingslip.saveCustomerShippingLotNumberInfo" ) );
                for( int i=0; i < lineItemCount; i++ )
                {
                    finalInventoryDataList = ( ArrayList ) finalInventoryMap.get ( ( ( CustomerShippingLineItems ) lineItems.get(i) ).getLineItemId()+"" );

                    if( finalInventoryDataList != null )
                    {
                        finalInventoryDataListSize = finalInventoryDataList.size();

                        for( int j = 0; j < finalInventoryDataListSize; j++ )
                        {
                            inventoryData = (FinalInventoryData) finalInventoryDataList.get ( j ) ;
                            pstmt.setInt( 1, inventoryData.getCsLineItemId() );
                            pstmt.setInt( 2, inventoryData.getLotId() );
                            pstmt.setInt( 3, inventoryData.getQuantityPull() );
                            pstmt.addBatch();
                        }
                        pstmt.executeBatch();
                        pstmt.clearBatch();
                    }
                }

                pstmt.close();
                pstmt = null;
            }

            // Step 4.5: Update Backlog_Master with the Quantity Shipped
            cstmt = conn.prepareCall ( SqlQueryMappings.get ( "operations.packingslip.updateBacklogMaster" ) );
            cstmt.setInt ( 1, packingSlipForm.getCustomerShippingId() );
            cstmt.executeUpdate();

            cstmt.close();
            cstmt = null;

            try
            {
                dWeight = Double.parseDouble( packingSlipForm.getWeight() );
            }
            catch( Exception e ) { dWeight = 0.0; }

            try
            {
                grossWeight = Double.parseDouble( packingSlipForm.getGrossWeight() );
            }
            catch( Exception e ) { grossWeight = 0.0; }

            if( packingSlipForm.getEtd() != null && packingSlipForm.getEtd().trim().length() == 0 )
            {
                packingSlipForm.setEtd( null );
            }
            if( packingSlipForm.getEta() != null && packingSlipForm.getEta().trim().length() == 0 )
            {
                packingSlipForm.setEta( null );
            }

             // Step 1:Update packingslip status
            cstmt = conn.prepareCall ( SqlQueryMappings.get ( "operations.packingslip.updatePackingSlipStatus" ) );
            cstmt.setInt 	( 1, packingSlipForm.getCustomerShippingId() );
            cstmt.setString ( 2, CUSTOMER_SHIPPING_STATUS_SHIPPED );
            cstmt.setInt 	( 3, packingSlipForm.getNoOfBoxesAsInt() ) ;
            cstmt.setString ( 4, packingSlipForm.getTrackingAsString() );
            cstmt.setString ( 5, packingSlipForm.getShippedVia() );
            cstmt.setDouble ( 6, dWeight);
            cstmt.setString ( 7, packingSlipForm.getContactPerson() );
            cstmt.setString ( 8, packingSlipForm.getInvNo() );
            cstmt.setString ( 9, packingSlipForm.getShipDate() );
            cstmt.setString ( 10, packingSlipForm.getShippingInstructions() );
            cstmt.setString ( 11, packingSlipForm.getComments() );
            cstmt.setString ( 12, packingSlipForm.getFreightInstructions() );
            cstmt.setDouble ( 13, grossWeight );
            cstmt.setString ( 14, packingSlipForm.getWeightType() );
            cstmt.setString ( 15, packingSlipForm.getPackageDimension() );
            cstmt.setString ( 16, packingSlipForm.getUomPkgDimension() );
            cstmt.setString ( 17, packingSlipForm.getMawb() );
            cstmt.setString ( 18, packingSlipForm.getHawb() );
            cstmt.setString ( 19, packingSlipForm.getFlightNumberOne() );
            cstmt.setString ( 20, packingSlipForm.getFlightNumberTwo() );
            cstmt.setString ( 21, packingSlipForm.getEtd() );
            cstmt.setString ( 22, packingSlipForm.getEta() );

            cstmt.executeUpdate();

            cstmt.close();
            cstmt = null;

            // Step 2: Update status in Customer_Open_Order_Line_Item table
            cstmt = conn.prepareCall ( SqlQueryMappings.get ( "operations.packingslip.updateOpenOrderLineItemStatus" ) );
            cstmt.setInt 	( 1, packingSlipForm.getCustomerShippingId() );
            cstmt.setString ( 2, CUSTOMER_SHIPPING_STATUS_SHIPPED );
            cstmt.setString ( 3, userId );
            cstmt.executeUpdate();
            
            cstmt.close();
            cstmt = null;

			/*
			Added This code for the POINT OF SALES. This is for the distributors network. Where the Company Shippes the Inventory to the Distributor.
			And the Shippiment is not Billed until the Distributor sells this inventory to another Customer.
			
			Until the Distributor ships this material to another customer, the inventory report will show as the inventory available in the Distributor Location.
			when ever the material is shipped to Distributor we are storing the inventory information in a new Table
			*/

			pstmt = conn.prepareStatement( "{ call SP_Update_Distributor_Inventory( ? ) }" );
			pstmt.setInt( 1, packingSlipForm.getCustomerShippingId() );
			pstmt.executeUpdate();
            pstmt.close();
            pstmt = null;
            String errorMsg = null;
            if ( packingSlipForm.isConsignment() )
            {
                cstmt = conn.prepareCall( "{ CALL SP_MOVE_INVENTORY_TO_CONSIGNMENT_LOCATION( ?, ? ) }" );
                cstmt.setInt( 1, packingSlipForm.getCustomerShippingId() );
                cstmt.registerOutParameter( 2, java.sql.Types.VARCHAR );
                cstmt.executeUpdate();

                errorMsg = cstmt.getString(2);
                if ( errorMsg != null && errorMsg.trim().length() > 0 )
                {
                    throw new ScalarException ( errorMsg );
                }
            }

            success = true;

         }
         catch (Exception sqle)
         {
            sqle.printStackTrace();             
            throw new ScalarException (sqle.getMessage(), sqle);
         }
         finally
         {
            try
            {
                if( dbTransaction == null )
                {
                    if ( conn != null )
                    {
                        if (success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                else
                {
                    if (success == true)
                    {
                        dbTransaction.commit();
                    }
                    else
                    {
                        dbTransaction.rollback();
                    }
                }
             }
             catch (SQLException ignore) {}

            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
             if (cstmt != null)
             {
                 try { cstmt.close(); } catch (SQLException ignore){}
             }
             if (conn != null)
             {
                 try { conn.close(); } catch (SQLException ignore){}
             }
         }
    }



    /**
     * @param locationId
     * @return
     */
   /* private static String getVendorNameOrCity( int locationId ) throws ScalarException
    {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null ;
		boolean success = false;
		String vendor = "";
		String city = "";
		String location = "";

		try
		{
			conn = DataSource.getConnection();

			// Step 1
			// Update Customer_Shipping Table
			//pstmt = conn.prepareStatement ( "select L_Vendor_Name , a.City from Location l, Vendor v, Address a where l.Address_ID = ? and l.Vendor_ID = v.Vendor_ID and l.Address_ID = a.Address_ID " );
            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "operations.packingslip.getVendorNameAndCityByAddressId" ) ); // Added by Krishna 28 June 2004, SqlQueryMappings
			pstmt.setInt ( 1, locationId );
			rs = pstmt.executeQuery();

			if( rs.next() )
			{
				vendor = rs.getString( "L_Vendor_Name" );
				city   = rs.getString( "City" );
			}

			if( city != null && ( city.trim().length() > 0 ) )
			{
				location = city;
			}
			else if ( vendor != null && ( vendor.trim().length() > 0 ) )
			{
				location = vendor;
			}

			success = true;
		}
		catch ( Exception sqle ) {
			throw new ScalarException ( "Could not update customer shipping information into database", sqle );
		}
		finally
		{
			try
			{
				if ( conn != null )
				{
					if ( success == true ) {	conn.commit();	}
					else {	conn.rollback(); }
				}
			}
			catch ( SQLException ignore ) {}

			if ( pstmt != null )
			{
				try { pstmt.close(); } catch ( SQLException ignore ){}
			}
			if ( conn != null )
			{
				try { conn.close(); } catch ( SQLException ignore ){}
			}
		}
		return location;
    }*/

    /**
     * @param packingSlipForm
     * @param currentUser
     */
    public static void updateCustomerShippingInvNoAirwayBillno( PackingSlipForm packingSlipForm, String currentUser ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try
        {
            conn = DataSource.getConnection();

            // Step 1
            // Update Customer_Shipping Table
            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "operations.packingslip.updateCustomerShippingForInvNo" ) );

            pstmt.setString ( 1, packingSlipForm.getTrackingAsString() );
            pstmt.setString ( 2, packingSlipForm.getInvNo() );
            pstmt.setString ( 3, currentUser );
            pstmt.setString ( 4, packingSlipForm.getShippingInstructions() );
            pstmt.setString ( 5, packingSlipForm.getComments() );
            pstmt.setString ( 6, packingSlipForm.getFreightInstructions() );
            pstmt.setInt 	( 7, packingSlipForm.getCustomerShippingId() );

            pstmt.executeUpdate();

            success = true;
        }
        catch ( Exception sqle ) {
            throw new ScalarException ( "Could not update customer shipping information into database", sqle );
        }
        finally
        {
            try
            {
                if ( conn != null )
                {
                    if ( success == true ) {	conn.commit();	}
                    else {	conn.rollback(); }
                }
            }
            catch ( SQLException ignore ) {}

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ){}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ){}
            }
        }
    }

    /**
     * This Method is used to fetch the PackingSlips accordingto seaarch Condition
     * used in
     * 1. SearchPackingSlipAction
     */
    public static ArrayList loadAllPackingSlipsForSearch( SearchPackingSlipForm searchForm, String scheduledBefore, String shippedAfter ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList packingSlipList = new ArrayList();
        //String statusCheck = null;
        //String sqlQuery = "";
        try
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement ( "{ Call sp_search_packing_slip(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }" );
            pstmt.setString     ( 1, searchForm.getSalesOrderNumber() );
            pstmt.setInt        ( 2, searchForm.getCustomerId() );
            pstmt.setString     ( 3, searchForm.getCustomerPo( ) );
            pstmt.setString     ( 4, searchForm.getSePsStatus() );
            pstmt.setInt        ( 5, searchForm.getAddressID() );
            pstmt.setInt        ( 6, searchForm.getManufacturingPartNo() );
            pstmt.setString 	( 7, searchForm.getYear() );
            pstmt.setInt        ( 8, searchForm.getOrderType() );
            pstmt.setString     ( 9, scheduledBefore );
            pstmt.setString     ( 10, shippedAfter );
            pstmt.setInt        ( 11, searchForm.isLineItemWise() ? 0 : 1 );

            rset = pstmt.executeQuery();

            PackingSlip packingSlip = null;
            int salesOrderId = 0;

            while( rset.next() )
            {
                packingSlip = new PackingSlip();
                salesOrderId = rset.getInt( "SalesOrder_Id" );
                packingSlip.setCustomerShippingId( rset.getInt( "CustomerShipping_Id" ) );
                packingSlip.setSalesOrderId( salesOrderId );
                packingSlip.setLabel( rset.getString( "Label" ) );
                packingSlip.setSalesOrderNo( rset.getString( "SalesOrder_No" ) );
                packingSlip.setCustomerName( rset.getString( "C_Name" ) );
                packingSlip.setCustomerPoNo( rset.getString( "SO_Customer_PO_number" ) );
                //statusCheck = rset.getString( "CST_Status" );
                packingSlip.setStatus( rset.getString( "CST_Status" ) );
                packingSlip.setInvNo( rset.getString( "Inv_No" ) );
                packingSlip.setLocation( rset.getString( "location" ) );
                packingSlip.setIsDropShipment( rset.getInt( "CST_IS_Drop_Shipment" ) );
                packingSlip.setMaxScheduleDate( StringUtils.formatDateToString(rset.getDate("MAX_SCHEDULE_DATE")) );
                packingSlip.setLineItemNo( rset.getInt ( "COOL_LINE_ITEM_NO" ) );
                packingSlip.setPartNumber( rset.getString ( "PART_NUMBER" ) );
                packingSlip.setMrktPartNumber( rset.getString ( "MRKT_PART_NUMBER" ) );
                packingSlip.setQty( rset.getInt ( "COOL_QTY" ) );
                packingSlip.setPulledQty( rset.getInt ( "PULLED_QTY" ) );
                packingSlip.setReadyForConfirm(rset.getInt("IS_READY_FOR_CONFIRM")==0);
                packingSlipList.add( packingSlip );
            }

        }
        catch (Exception e)
        {
            e.printStackTrace();
            throw new ScalarException ("Could not retrieve PackingSlip list from database", e);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
        return packingSlipList;
    }

    /**
     * @param packingSlipId
     * @param lineItemId
     * @return
     */
    public static HashMap loadCustomerShippingLotNumberData ( int packingSlipId, int lineItemId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        HashMap csLotNumberDataMap = new HashMap();

        try
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareCall (SqlQueryMappings.get ("operations.packingslip.loadCustomerShippingLotNumberData"));
            pstmt.setInt (1, packingSlipId);
            pstmt.setInt (2, lineItemId);
            rset = pstmt.executeQuery();

            int lotId	 	= -1;
            int qtyPull 	= -1;
            int cslitId 	= -1;
            String lotNumber = null;
            String fabLotNumber = null;
            String dateCode  = null;

            while ( rset.next() )
            {
                cslitId 	= rset.getInt ("CSLIT_ID");
                lotId 		= rset.getInt ("Lot_ID");
                qtyPull 	= rset.getInt ("CSLNT_Qty_Pull");
                lotNumber 	= rset.getString ("Lot_Number");
                fabLotNumber = rset.getString( "Fab_lot_Number" );
                dateCode    = rset.getString("lot_date_code");
                csLotNumberDataMap.put (lotId +"", new CustomerShippingLotNumberData ( cslitId, lotId, lotNumber, fabLotNumber, qtyPull, dateCode ) );
            }
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            System.out.println ("Could not load CustomerShipping Lot Number information for packingslip Id- " + packingSlipId + ", and line itemID - " + lineItemId);
            throw new ScalarException ("Could not load CustomerShipping Lot Number information for packingslip Id- " + packingSlipId + ", and line itemID - " + lineItemId, e);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return csLotNumberDataMap;
    }

    /*	This method takes the Customer Shipping ID
      *  and returns hashMap of all the LineItems in that packingSlip
      *  Containing the arraylist of the Quantity pulled
      *  Used in
      *  1. NewPackingSlip action for View under cancel and confirm conditions
      */
    public static HashMap loadLotNumberDataByCustomerShippingId ( int customerShippingId ) throws ScalarException
    {
        Connection conn 		= null;
        PreparedStatement pstmt = null;
        ResultSet rset 			= null;
        HashMap csLotNumberDataMap = new HashMap();
        ArrayList lineItems		   = null;
        FinalInventoryData fid 	   = null;
        int lineItemId			   = -1;

        try
        {
            conn 	= DataSource.getConnection();
            //pstmt 	= conn.prepareCall ( "select CSLIT_ID from Customer_Shipping_Line_Item_Table where CustomerShipping_ID = ? " );
            pstmt 	= conn.prepareCall ( SqlQueryMappings.get ( "operations.packingslip.getCustomerShippingLineItemId" ) ); // Added by Krishna 28 June 2004, SqlQueryMappings
            pstmt.setInt ( 1, customerShippingId );
            rset 	= pstmt.executeQuery();

            while ( rset.next() )
            {
                csLotNumberDataMap.put ( rset.getString("CSLIT_ID"), "" );
            }

            rset.close();
            rset = null;
            pstmt.close();
            pstmt = null;
            //Commented by Krishna Prasad on 31 May 2004
            //pstmt = conn.prepareStatement( "select CSLNT.Lot_ID, CSLNT_Qty_Pull, Lot_Number from Customer_Shipping_Lot_Number_Table CSLNT, Lot_Table where CSLNT.Lot_ID = Lot_Table.Lot_ID and CSLIT_ID = ?" );
            //Added by Krishna Prasad on 31 May 2004
            //pstmt = conn.prepareStatement( "select CSLNT.Lot_ID, CSLNT_Qty_Pull, Lot_Number, Lot_Date_Code, ProcessStep_ID from Customer_Shipping_Lot_Number_Table CSLNT, Lot_Table where CSLNT.Lot_ID = Lot_Table.Lot_ID and CSLIT_ID = ?" );
            pstmt = conn.prepareStatement(  SqlQueryMappings.get ( "operations.packingslip.loadLotAndQuantityPulledDetails" ) ); // Added by Krishna 28 June 2004, SqlQueryMappings

            Iterator it = csLotNumberDataMap.keySet().iterator();

            while( it.hasNext() )
            {
                lineItems	= new ArrayList();
                lineItemId	= Integer.parseInt( (String)it.next() );

                pstmt.setInt( 1, lineItemId );
                rset = pstmt.executeQuery();

                while ( rset.next() )
                {
                    fid = new FinalInventoryData();
                    fid.setLotId		( rset.getInt( "Lot_ID" ) );
                    fid.setQuantityPull	( rset.getInt( "CSLNT_Qty_Pull" ) );
                    fid.setLotNumber	( rset.getString( "Lot_Number" ) );
                    fid.setFabLotNumber ( rset.getString( "Fab_lot_Number" ) );
                    fid.setDateCode     ( rset.getString( "Lot_Date_Code" ) );//modified by Krishna Prasad on 31 May 2004
                    fid.setMktPartNumber( rset.getString( "Part_Number" ) );
                    fid.setLineItemId   ( rset.getInt( "LineItem_ID" ) );
                    fid.setLotEnteredBy ( rset.getString( "Lot_Entered_By" ) );
                    fid.setIsReduceBalance( rset.getInt( "Lot_Entered_By" ) );
                    fid.setCsLineItemId ( lineItemId );
                    //System.out.println(lineItemId + "-----------------------------PS-ACTION-fiData = " + fid);
                    lineItems.add( fid );
                }
                csLotNumberDataMap.put ( lineItemId+"".trim(), lineItems );
            }
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not loadLotNumberDataByCustomerShippingId information" , e );
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
        return csLotNumberDataMap;
    }

   //Added by Krishna Prasad on 31 May 2004
    public static ArrayList loadInnerOuterBoxById (PackingSlipForm psf) throws ScalarException
    {
        Connection conn 		= null;
        PreparedStatement pstmt = null;
        ResultSet rset 			= null;

        int unitsPerTray        = 0;
        int traysPerInnerBox    = 0;
        int innerBoxsPerOuterBox= 0;

        HashMap inventoryMap	= psf.getFinalInventoryDataMap();
        ArrayList lineItemsList	= psf.getLineitems();
        //CustomerShippingLineItems lineItem  = null;
        ArrayList finalLineItemsList   		= new ArrayList();

        try
           {
            conn 	= DataSource.getConnection();
            //pstmt 	= conn.prepareCall ( "SELECT PP.ProcessStep_ID, PP.Parameter_Key, PP.Parameter_Units, PP.Parameter_Value FROM Process_Parameter PP INNER JOIN Product P ON PP.ProcessStep_ID = P.ProcessStep_ID WHERE ( P.Product_ID = ? )" );
               pstmt 	= conn.prepareCall ( SqlQueryMappings.get ( "operations.packingslip.loadProcessStepParametersByProductId" ) ); // Added by Krishna 28 June 2004, SqlQueryMappings

            for ( int i = 0; i < lineItemsList.size() ; i++ )
            {
                int dbParameterValue 			= 0;
                //int finalInventoryDataListSize 	= -1;
                String dbParameterKey 			= "";
                String dbStrParameterValue 		= null ;
                ArrayList InventoryList 		= null;
                //FinalInventoryData inventoryData 	= null;
                CustomerShippingLineItems cLineItem = null;

                cLineItem = ( CustomerShippingLineItems ) lineItemsList.get(i);

                if( inventoryMap != null )
                {
                    pstmt.setInt ( 1, cLineItem.getProduct_ID() );
                    rset 	= pstmt.executeQuery();
                    while ( rset.next() )
                    {
                        dbParameterValue	= 0;
                        dbParameterKey 		= rset.getString( "Parameter_Key" );
                        dbStrParameterValue = rset.getString( "Parameter_Value" ) ;

                        if ( dbStrParameterValue != null && dbStrParameterValue.trim().length() > 0 )
                        {
                            if ( dbParameterKey.equalsIgnoreCase ( "Units Per Tray" ) || dbParameterKey.equalsIgnoreCase ( "Trays Per InnerBox" ) || dbParameterKey.equalsIgnoreCase ( "InnerBoxes Per OuterBox" ) )
                            {
                                try
                                {
                                    dbParameterValue = Integer.parseInt( dbStrParameterValue ) ;
                                }
                                catch (NumberFormatException nfe) {}
                            }
                        }

                        if( dbParameterKey.equalsIgnoreCase ( "Units Per Tray" ) )
                        {
                            cLineItem.setUnitsPTrays ( dbParameterValue );
                        }
                        else if( dbParameterKey.equalsIgnoreCase ( "Trays Per InnerBox" ) )
                        {
                            cLineItem.setTraysPInnerBoxes ( dbParameterValue );
                        }
                        else if( dbParameterKey.equalsIgnoreCase ( "InnerBoxes Per OuterBox" ) )
                        {
                            cLineItem.setInnerPOuterBoxes ( dbParameterValue );
                        }
                        else if( dbParameterKey.equalsIgnoreCase ( "rrmmcc" ) )
                        {
                            cLineItem.setRrmmcc ( rset.getString("Parameter_Value") );
                        }
                        else if( dbParameterKey.equalsIgnoreCase ( "ms level" ) )
                        {
                            cLineItem.setMsLevel ( rset.getString("Parameter_Value") );
                        }
                        else if( dbParameterKey.equalsIgnoreCase ( "safe hours" ) )
                        {
                            cLineItem.setSafeHours ( rset.getString("Parameter_Value") );
                        }
                        else if( dbParameterKey.equalsIgnoreCase ( "seal date" ) )
                        {
                            cLineItem.setSealDate ( rset.getString("Parameter_Value") );
                        }
                        else if( dbParameterKey.equalsIgnoreCase ( "made in country" ) )
                        {
                            cLineItem.setMadeInCountry ( rset.getString("Parameter_Value") );
                        }
                    }
                    unitsPerTray            = cLineItem.getUnitsPTrays();
                    traysPerInnerBox        = cLineItem.getTraysPInnerBoxes() ;
                    innerBoxsPerOuterBox    = cLineItem.getInnerPOuterBoxes();

                    InventoryList = ( ArrayList ) inventoryMap.get ( cLineItem.getCustomerShippingLineItemId() + "" );
                    cLineItem.setInnerAndOuterBoxList( InventoryList );

                    int totalQtyPulled 	= 0;
                    int totalTrays 		= 0;
                    int totalInnerBoxes = 0;
                    int totalOuterBoxes = 0;

                    totalQtyPulled  = cLineItem.getQtyPulled();

                    if( unitsPerTray > 0 )
                    {
                        totalTrays  =  totalQtyPulled / unitsPerTray;
                        if( totalQtyPulled % unitsPerTray > 0 )
                        {
                            totalTrays++;
                        }
                    }
                    else
                    {
                        totalTrays = 0;
                    }

                    //if( unitsPerTray == 0 )
                    //{
                    //   totalTrays = totalQtyPulled;
                    //}

                    if( ( traysPerInnerBox > 0 ) )
                    {
                        totalInnerBoxes =  totalTrays / traysPerInnerBox;
                        if( totalTrays % traysPerInnerBox > 0 )
                        {
                            totalInnerBoxes++;
                        }
                    }
                    else
                    {
                        totalInnerBoxes = 0;
                    }

                    if( ( innerBoxsPerOuterBox > 0 ) )
                    {
                        totalOuterBoxes = totalInnerBoxes / innerBoxsPerOuterBox;
                        if( totalInnerBoxes % innerBoxsPerOuterBox > 0 )
                        {
                            totalOuterBoxes++;
                        }
                    }
                    else
                    {
                        totalOuterBoxes = 0;
                    }

                    //String trays 	  = totalTrays + "";
                    //String innerBoxes = totalInnerBoxes + "";
                    //String outerBoxes = totalOuterBoxes + "";
                    //unitsPerTray            = cLineItem.getUnitsPTrays();
                    //traysPerInnerBox        = cLineItem.getInnerPOuterBoxes() ;
                    //innerBoxsPerOuterBox    = cLineItem.getInnerPOuterBoxes();

                    cLineItem.setTotalTrays     ( totalTrays + "" );
                    cLineItem.setTotalInnerBoxes( totalInnerBoxes + "" );
                    cLineItem.setTotalOuterBoxes( totalOuterBoxes + "" );
                }
                finalLineItemsList.add(cLineItem);
            }
        }
           catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not loadLotNumberDataByCustomerShippingId information" , e );
        }
        finally
        {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
        return finalLineItemsList;
    }


    /*	This method takes the Customer Shipping ID
      *  and returns hashMap of all the LineItems in that packingSlip
      *  Containing the arraylist of the Quantity pulled
      *  Used in
      *  1. NewPackingSlip action for View under cancel and confirm conditions
      */
    public static HashMap loadLotDataByCustomerShippingId ( int customerShippingId ) throws ScalarException
    {
        Connection conn 		= null;
        PreparedStatement pstmt = null;
        ResultSet rset 			= null;
        HashMap csLotNumberDataMap = new HashMap();
        ArrayList lineItems		   = null;
        FinalInventoryData fid 	   = null;
        int lineItemId			   = -1;
        String city	 		   	   = null;

        try
        {
            conn 	= DataSource.getConnection();
            //pstmt 	= conn.prepareCall ( "select CSLIT_ID from Customer_Shipping_Line_Item_Table where CustomerShipping_ID = ? " );
            pstmt 	= conn.prepareCall ( SqlQueryMappings.get ( "operations.packingslip.getCustomerShippingLineItemId" ) ); // Added by Krishna 28 June 2004, SqlQueryMappings
            pstmt.setInt ( 1, customerShippingId );
            rset 	= pstmt.executeQuery();

            while ( rset.next() )
            {
                csLotNumberDataMap.put ( rset.getString("CSLIT_ID"), "" );
            }

            rset.close();
            rset = null;
            pstmt.close();
            pstmt = null;

            //pstmt = conn.prepareStatement( "select CSLNT.Lot_ID, CSLNT_Qty_Pull, Lot_Number, IL_Qty_In, IL_Qty_Balance, City, L_Vendor_Name, Part_Number from Customer_Shipping_Lot_Number_Table CSLNT, Lot_Table, Inventory_Lot, Location, Address, Product where CSLNT.Lot_ID = Lot_Table.Lot_ID and Lot_Table.Lot_ID = Inventory_Lot.Lot_Id and Lot_Table.Location_id = Location.Location_Id and Location.Address_id = Address.Address_Id and Lot_Table.ProcessStep_id = Product.ProcessStep_Id  and CSLIT_ID = ?" );
            pstmt = conn.prepareStatement( SqlQueryMappings.get ( "operations.packingslip.loadLotAndInventoryDetailsByLotId" ) ); // Added by Krishna 28 June 2004, SqlQueryMappings

            Iterator it = csLotNumberDataMap.keySet().iterator();

            while( it.hasNext() )
            {
                lineItems	= new ArrayList();
                lineItemId	= Integer.parseInt( (String)it.next() );

                pstmt.setInt( 1, lineItemId );
                rset = pstmt.executeQuery();

                while ( rset.next() )
                {
                    fid = new FinalInventoryData();
                    fid.setLotId		( rset.getInt( "Lot_ID" ) );
                    fid.setQuantityPull	( rset.getInt( "CSLNT_Qty_Pull" ) );
                    fid.setQuantityIn	( rset.getInt( "IL_Qty_In" ) );
                    fid.setBalance		( rset.getInt( "IL_Qty_Balance" ) );
                    fid.setLotNumber	( rset.getString( "Lot_Number" ) );
                    fid.setMktPartNumber( rset.getString( "Part_Number" ) );

                    city = rset.getString( "City" );
                    if( city == null || city.length() < 1 )
                    {
                        city = rset.getString( "L_Vendor_Name" );
                    }
                    fid.setVendorLocation( city );

                    lineItems.add( fid );
                }
                csLotNumberDataMap.put ( lineItemId+"", lineItems );
            }
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not loadLotNumberDataByCustomerShippingId information" , e );
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return csLotNumberDataMap;
    }

    /*
      *  This Method is used to fetch the Shipping Contact person,
      *  This has to be removed in the future and placed in
      *  SalesOrderDAO.loadSalesOrder ( salesOrderId ) in GeneratePaclingSlipAction
      */

    public static String getShippingOrBillingContactPerson ( DBTransaction dbTransaction, String salesOrderId ) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        String contactPerson = "";

        try
        {
            Integer.parseInt( salesOrderId );
        }
        catch( NumberFormatException e )
        {
            return contactPerson;
        }

        try
        {
            if(dbTransaction != null)
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "operations.packingslip.getShippingContactPerson" ) );
            pstmt.setInt ( 1, Integer.parseInt( salesOrderId.trim() ) );
            rset = pstmt.executeQuery();

            while (rset.next())
            {
                contactPerson = rset.getString ("contact_person");
            }
        }
        catch (Exception e)
        {
            throw new ScalarException ("Could not load Shipping Contact Person Details for Sales Order Id - " + salesOrderId, null);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
        return contactPerson;
    }

    /*  This metnod is used to get the Number of PackingSlip genrated for the
      *  Sales Order. This is used to prepare the packing Slip lable in Generate PS
      */
    public static int getTotalNumberOfPackingSlipsGenerated (String salesOrderId) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        int total = 0;

        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareCall (SqlQueryMappings.get ("operations.packingslip.getTotalNumberOfPackingSlipsGenerated"));
            pstmt.setString (1, salesOrderId);
            rset = pstmt.executeQuery();

            if (rset.next()) {
                total = rset.getInt ("total");
            }
        }
        catch (Exception e) {
            throw new ScalarException ("Could not retrieve the total number of PackingSlip generated for the sales order ID: " + salesOrderId, e);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return total;
    }

    /*
      * This method is used to get the LineItemIds and Status
      * Used in
      * 1. SearchPackingSlipAction
      */

    public static HashMap getLineItemStatusBySalesOrderId ( String salesOrderNo ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;

        HashMap coolStatusMap = new HashMap();

        try
        {
            conn = DataSource.getConnection();
            if( salesOrderNo != null )
            {
                pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.packingslip.getLineItemStatusBySalesOrderId" ) );
                pstmt.setString ( 1, salesOrderNo );
            }

            rset = pstmt.executeQuery();

            while( rset.next() )
            {
                coolStatusMap.put( rset.getInt( "LineItem_ID" )+"", rset.getString( "COOL_Status" ) );
            }
        }
        catch( Exception e )
        {
            throw new ScalarException ( "Could not retrieve LineItem status for SalesOrder " + salesOrderNo, e );
        }
        finally
        {
            if ( rset != null ) {
                try { rset.close(); } catch ( SQLException ignore ) {}
            }
            if ( pstmt != null ) {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null ) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return coolStatusMap;
    }

    // added by balaram

    // BR, Mar 13, 2004
    public static ShippingAdviseForm loadShippingAdviceById( int customerShippingId, User user ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        String paletteCount = null;
        String customerPONo = null;
        String grossWeight  = null;
        ShippingAdviseForm shippingAdviseForm = null;


        try
        {
            conn = DataSource.getConnection();
            //pstmt = conn.prepareStatement( "SELECT CST.LABEL[NUMBER], CST.CUSTOMERSHIPPING_ID, SO.SALESORDER_ID, SO.SALESORDER_NO, CUST.C_NAME, CST_VENDOR_PO_NUMBER, CST.INV_NO, CST.CST_FOB, CST.CST_BOX_COUNT, CST.CST_WEIGHT, CST.CST_AW_BILL_NUMBER, CST.CST_METHOD, CST.CST_FLIGHT_NUMBER, CST.CST_FLIGHT_ROUTE, CST.CST_ETD, CST.CST_ETA, CST.CST_HAWB, CST.CST_MAWB, CST.CST_STATUS, CST.CST_PALETTE_COUNT, CST.CST_IS_Drop_Shipment, CST.CST_Location_Address_Id, CST.CST_Shipping_Instructions,  CST.CST_Ship_Date, CST.CST_comments FROM CUSTOMER_SHIPPING_TABLE CST, SALES_ORDER SO, CUSTOMER CUST WHERE CST.CUSTOMERSHIPPING_ID = ? AND CST.SALESORDER_ID = SO.SALESORDER_ID AND SO.CUSTOMER_ID = CUST.CUSTOMER_ID" );
            pstmt = conn.prepareStatement( SqlQueryMappings.get ( "operations.packingslip.vendorInterface.loadShippingAdviceById" ) );
            pstmt.setInt( 1, customerShippingId );
            rset = pstmt.executeQuery();

            if( rset.next() )
            {
                shippingAdviseForm = new ShippingAdviseForm( rset.getString( "NUMBER" ),
                                                             rset.getInt( "CUSTOMERSHIPPING_ID" ),
                                                             rset.getInt( "SALESORDER_ID" ),
                                                             rset.getString( "SALESORDER_NO" ),
                                                             rset.getString( "C_NAME" ),
                                                             null /*rset.getString( "CST_VENDOR_PO_NUMBER" )*/,
                                                             rset.getString( "INV_NO" ),
                                                             rset.getString( "CST_FOB" ),
                                                             rset.getString( "CST_BOX_COUNT" ),
                                                             rset.getString( "CST_Weight" ),
                                                             StringUtils.toStringArray( rset.getString( "CST_AW_BILL_NUMBER" ), ";" ),
                                                             rset.getString( "CST_METHOD" ),
                                                             rset.getString( "CST_FLIGHT_NUMBER" ),
                                                             rset.getString( "CST_FLIGHT_ROUTE" ),
                                                             StringUtils.formatDateToString( rset.getDate ( "CST_ETD" ) ),
                                                             StringUtils.formatDateToString( rset.getDate ( "CST_ETA" ) ),
                                                             rset.getString( "CST_HAWB" ),
                                                             rset.getString( "CST_MAWB") );

                shippingAdviseForm.setStatus ( rset.getString( "CST_STATUS" ) ); // Added by Krishna 08 June 2004
                paletteCount = rset.getString( "CST_PALETTE_COUNT" );

                if( "0".equalsIgnoreCase( paletteCount ) )
                {
                    shippingAdviseForm.setPaletteCount( "" );  //Added by Varma 17 oct 2004
                }
                else
                {
                    shippingAdviseForm.setPaletteCount( paletteCount );  //Added by Varma 17 oct 2004
                }

                shippingAdviseForm.setDropShippment( !rset.getBoolean( "CST_IS_Drop_Shipment" ) );
                shippingAdviseForm.setShippingFromLocationId( rset.getInt ( "CST_Location_Address_Id") );
                shippingAdviseForm.setShippingInstructions	( rset.getString ( "CST_Shipping_Instructions" ) );
                shippingAdviseForm.setShipDate ( StringUtils.formatDateToString( rset.getDate ( "CST_Ship_Date" ) ) );
                shippingAdviseForm.setComments( rset.getString ( "CST_comments" ) );
                shippingAdviseForm.setPackedby( rset.getString("CST_Packedby") );
                shippingAdviseForm.setTitle( rset.getString("CST_Title") );
                shippingAdviseForm.setFreightInstructions( rset.getString("CST_Freight_Instructions") );
                shippingAdviseForm.setOdmId( rset.getInt( "CST_ODM_ID" ) );
                shippingAdviseForm.setContactPerson ( rset.getString ( "CST_Shipping_Contact_Person" ) );
                shippingAdviseForm.setShippingToAddressId 	( rset.getInt ( "CST_TO_Address_ID" ) );
                shippingAdviseForm.setShippingFromAddressId( rset.getInt ( "CST_From_Address_ID" ) );
                shippingAdviseForm.setBillingAddressId(rset.getInt ( "CST_Billing_Address_Id" ) );
                shippingAdviseForm.setSupplierNumber( rset.getString ( "CST_Supplier_Number" ) );
                shippingAdviseForm.setOdmSupplierNumber( rset.getString ( "CST_ODM_Supplier_Number" ) );
                shippingAdviseForm.setLetterOfCreditNo( rset.getString ( "SO_Letter_Of_Credit_No" ) );
                //SO.SO_LC_Issuing_Bank, CST.CST_Gross_Weight, CST.CST_Package_Dimension, CST.CST_UOM_Weight, CST_UOM_Pkg_Dimension, CST_MAWB, CST_HAWB, CST_FLIGHT_NUMBER, CST_FLIGHT_NUMBER_TWO, CST_ETD, CST_ETA
                grossWeight = rset.getString ( "CST_Gross_Weight" );
                if( ( "0.0".equalsIgnoreCase( grossWeight ) == false ) && ( "0.00".equalsIgnoreCase( grossWeight ) == false ) )
                {
                    shippingAdviseForm.setGrossWeight( grossWeight );
                }
                shippingAdviseForm.setPackageDimension ( rset.getString ( "CST_Package_Dimension" ) );
                shippingAdviseForm.setWeightType ( rset.getString ( "CST_UOM_Weight" ) );
                shippingAdviseForm.setUomPkgDimension ( rset.getString( "CST_UOM_Pkg_Dimension" ) );
                shippingAdviseForm.setFlightNumberOne( shippingAdviseForm.getFlightNumber() );
                shippingAdviseForm.setFlightNumberTwo( rset.getString( "CST_FLIGHT_NUMBER_TWO" ) );
            }
            shippingAdviseForm.setExternalUserId( user.getUserId() );

            rset.close();
            rset = null;
            pstmt.close();
            pstmt = null;

            shippingAdviseForm.setViewInventory( user.isActionAllowed ( Functionality.PACKING_SLIP, User.USER_ACTION_MODIFY));
            shippingAdviseForm.setModifyInventory( user.isActionAllowed ( Functionality.PACKING_SLIP, User.USER_ACTION_MODIFY ));
            shippingAdviseForm.setRunAdaptor( user.isActionAllowed ( Functionality.RUN_ADAPTOR, User.USER_ACTION_MODIFY ));
            
            conn.close();
            conn = null;

            SalesOrder salesOrder 	= SalesOrderDAO.loadSalesOrder ( shippingAdviseForm.getSalesOrderId() + "", null );
            shippingAdviseForm.setConsignment( "0".equals ( salesOrder.getIsConsignmentSales() ) );
            shippingAdviseForm.setCustomerNumber( salesOrder.getCustomerNumber() );
            if ( shippingAdviseForm.getOdmId() > 0 )
            {
                Customer odmCustomer = CustomerDAO.loadCustomerById ( shippingAdviseForm.getOdmId() );
                shippingAdviseForm.setOdm( odmCustomer.getCustomerName() );
            }
            if ( shippingAdviseForm.getBillingAddressId() == 0 )
            {
                //We are Assigning ShippingFromAddressId to  BillingAddressId because
                //new column called  CST_Billing_Address_Id is added to CST table on 26 May 2004
                //So Previously 'Billing Address' used to get from ShippingFromAddressId DB Field
                //But now we are getting from For CST_Billing_Address_Id DB Field, so prvious PS records we fixed like this
                //packingSlipForm.setBillingAddressId( packingSlipForm.getShippingFromAddressId() );
                shippingAdviseForm.setBillingAddressId( salesOrder.getBillingAddressId() );
            }
            shippingAdviseForm.setIsSample( salesOrder.getIsSample() );
            shippingAdviseForm.setBillingAddressObject ( CustomerDAO.getAddress ( shippingAdviseForm.getBillingAddressId() ) );
            shippingAdviseForm.setBillingAddress( shippingAdviseForm.getBillingAddressObject().getAddressAsString () );
            if ( shippingAdviseForm.getShippingToAddressId() > 0 )
            {
                shippingAdviseForm.setShippingAddressObject ( CustomerDAO.getAddress ( shippingAdviseForm.getShippingToAddressId() ) );
                shippingAdviseForm.setShippingAddress( shippingAdviseForm.getShippingAddressObject().getAddressAsString () );
            }
            if ( shippingAdviseForm.getShippingFromLocationId() == 0  )
            {
                //We are Assigning ShippingFromLocationId to  ShippingFromLocationId because
                //new column called  CST_Location_Address_Id is added to CST table on 26 May 2004
                //So Previously 'Location Id' used to get from ShippingFromAddressId DB Field
                //But now we are getting from For CST_Location_Address_Id DB Field, so prvious PS records we fixed like this
                shippingAdviseForm.setShippingFromLocationId( shippingAdviseForm.getShippingFromAddressId() );
            }
            shippingAdviseForm.setShippersAddressObject( CustomerDAO.getAddress( shippingAdviseForm.getShippingFromAddressId() ) );
            int vendorLocationId =  shippingAdviseForm.getShippingFromLocationId();
            int formAddressId = shippingAdviseForm.getShippingFromAddressId() ;
            VendorAddress va = new VendorAddress();
            if ( vendorLocationId > 0)
            {
                va = VendorDAO.getVendorNameAndAddress( vendorLocationId );
            }
            else
            {
                //We are Assigning ShippingFromLocationId to  ShippingFromLocationId because
                //new column called  CST_Location_Address_Id is added to CST table on 26 May 2004
                //So Previously 'Location Id' used to get from ShippingFromAddressId DB Field
                //But now we are getting from For CST_Location_Address_Id DB Field, so prvious PS records we fixed like this
                va = VendorDAO.getVendorNameAndAddress( formAddressId );
            }
            shippingAdviseForm.setVendorName( va.getVendorName() );
            shippingAdviseForm.setShippingVendorAddressObject(va);
            customerPONo = salesOrder.getCustomerPO();
            if ( customerPONo != null && customerPONo.trim().length() > 0 )
            {
                shippingAdviseForm.setPoNumber( customerPONo );
            }
            // Load the Client Information
            ClientInfo clientInfo = new ClientInfo();
            clientInfo = UserDAO.getClientAddressInfo(  );
            shippingAdviseForm.setClientInfo( clientInfo );
            shippingAdviseForm.setClientName( clientInfo.getClientName() );

            /*
            pstmt = conn.prepareStatement( "SELECT CSLNT.CSLIT_ID, LT.LOT_ID, LT.LOT_NUMBER, CSLNT_QTY_PULL, COOL.COOL_LINE_ITEM_NO, CSLNT.TRACKING_NUMBER FROM CUSTOMER_SHIPPING_LOT_NUMBER_TABLE CSLNT, CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT, CUSTOMER_SHIPPING_TABLE CST, LOT_TABLE LT, CUSTOMER_OPEN_ORDER_LINEITEM COOL WHERE CST.CUSTOMERSHIPPING_ID = ? AND CST.CUSTOMERSHIPPING_ID = CSLIT.CUSTOMERSHIPPING_ID AND CSLIT.CSLIT_ID = CSLNT.CSLIT_ID AND CSLIT.LINEITEM_ID = COOL.LINEITEM_ID AND CSLNT.LOT_ID = LT.LOT_ID" );
            pstmt.setInt( 1, customerShippingId );
            rset = pstmt.executeQuery();

            String trackingNumber = null;
            HashMap pulledLotMap = new HashMap();
            ArrayList lotWithTrackingNumberList = null;
            CustomerShippingLotNumberData pulledLot = null;

            while( rset.next() )
            {
                pulledLot = new CustomerShippingLotNumberData( rset.getInt( "CSLIT_ID" ),
                                                                      rset.getInt( "LOT_ID"),
                                                                      rset.getString( "LOT_NUMBER" ),
                                                                      rset.getInt( "CSLNT_QTY_PULL" ),
                                                                      rset.getString( "COOL_LINE_ITEM_NO" ),
                                                                      rset.getString( "TRACKING_NUMBER" ) );
                trackingNumber = pulledLot.getTrackingNumber();

                lotWithTrackingNumberList = ( ArrayList ) pulledLotMap.get( trackingNumber );
                if( lotWithTrackingNumberList == null )
                {
                    lotWithTrackingNumberList = new ArrayList();
                }

                lotWithTrackingNumberList.add( pulledLot );
                pulledLotMap.put( trackingNumber, lotWithTrackingNumberList );
            }
            shippingAdviseForm.setPulledLotMap( pulledLotMap );
            */
            if( conn != null )
            {
                conn.close();
                conn = null;
            }
            if( conn == null )
            {
                conn = DataSource.getConnection();
            }

            if ( shippingAdviseForm.isDropShippment() == true )
            {
                //pstmt = conn.prepareStatement ( "{ call sp_load_lineitems_for_packing_slip ( ?, ?, ? ) }" );
                pstmt = conn.prepareStatement ( SqlQueryMappings.get( "operations.packingslip.fetchCustomerShippingLineItemData" ) );

                pstmt.setInt( 1, shippingAdviseForm.getCustomerShippingId() );
                pstmt.setInt( 2, shippingAdviseForm.getSalesOrderId() );
                pstmt.setInt( 3, shippingAdviseForm.getShippingFromLocationId() );

                rset = pstmt.executeQuery();
                CustomerShippingLineItems cslt = null;

                ArrayList lineItemList = new ArrayList();
                String[] shippedPartNo = null;
                ArrayList subPartNoList = null;

                while(rset.next())
                {
                    cslt = new CustomerShippingLineItems();
                    cslt.setLineItemId	  ( rset.getInt( "lineItemId" ) );
                    cslt.setCustomerShippingLineItemId( rset.getInt( "CSLIT_Id" ) );
                    cslt.setQtyToShip	  ( rset.getInt( "qtyToShip" ) );
                    cslt.setSalesOrder_ID ( rset.getInt( "SalesOrder_ID" ) );
                    cslt.setLineitemNumber( rset.getInt( "COOL_Line_Item_No" ) );
                    cslt.setProduct_ID	  ( rset.getInt( "Product_ID" ) );
                    cslt.setCool_Qty	  ( rset.getInt( "COOL_Qty" ) );
                    cslt.setCool_Unit_Cost( rset.getFloat( "COOL_Unit_Cost" ) );
                    cslt.setCool_Amount	  ( rset.getFloat( "COOL_Amount" ) );
                    cslt.setQtyPulled	  ( rset.getInt( "Qty_Pulled" ) );
                    //cslt.setOrginalQtyPulled( 0 );  //This is set to zero because In PS Dropship issue , no inital qty pulled from lots
                    cslt.setOrginalQtyPulled( rset.getInt( "Original_Qty_Pulled" ) );

                    cslt.setCool_Requested_Date	( StringUtils.formatDateToString( rset.getDate( "COOL_Requested_Date" ), null ) );
                    cslt.setCool_Booking_Date	( StringUtils.formatDateToString( rset.getDate( "COOL_Booking_Date" ), null ) );
                    cslt.setCool_Sugg_Schedule_Date_With_Substitutables( StringUtils.formatDateToString( rset.getDate( "COOL_Sugg_Schedule_Date_With_Substitutables" ), null ) );
                    cslt.setCool_Sugg_Schedule_Date_Without_Substitutables( StringUtils.formatDateToString( rset.getDate( "COOL_Sugg_Schedule_Date_Without_Substitutables" ), null ) );
                    cslt.setCool_Schedule_Date	( StringUtils.formatDateToString( rset.getDate( "COOL_Schedule_Date" ), null ) );
                    cslt.setCool_Revised_Date	( StringUtils.formatDateToString( rset.getDate( "COOL_Revised_Date" ), null ) );
                    cslt.setCool_Is_Cancel	( rset.getBoolean( "COOL_Is_Cancel" ) );
                    cslt.setCool_Status		( rset.getString( "COOL_Status" ) );
                    cslt.setCool_Taxable	( rset.getBoolean( "COOL_Taxable" ) );
                    cslt.setCool_Description( rset.getString( "COOL_Description" ) );
                    cslt.setCool_PO_Line_Item_No( rset.getInt( "COOL_PO_Line_Item_No" ) );
                    cslt.setPart_Number		( rset.getString( "Part_Number" ) );
                    cslt.setMkt_Part_Number	( rset.getString( "Mkt_Part_Number" ) );
                    cslt.setCustomer_Part_Number( rset.getString( "Customer_Part_Number" ) );
                    cslt.setQty_Available 	( rset.getInt( "Qty_Available" ) );
                    cslt.setScheduleNumber	( rset.getString( "ScheduleNumber" ) );
                    cslt.setSoftwareVersion	( rset.getString( "Software_version" ) );
                    cslt.setUnits			( rset.getString( "Units" ) );
                    cslt.setDropShippment   ( shippingAdviseForm.isDropShippment() );
                    //cslt.setShipped_Part_Number ( getShippedPartNumbersByCsLineItemId  ( null , cslt.getCustomerShippingLineItemId(), cslt.getPart_Number(), "View" )  );
                    //cslt.setSubstitutablePartNumbers ( loadSubstitutablePartNumbersByCsLineItemId  ( null , cslt.getCustomerShippingLineItemId(), cslt.getPart_Number(), "View", cslt.getProduct_ID() ) );

                    subPartNoList = loadShippedPartNumbersByCsLineItemId ( null , cslt.getCustomerShippingLineItemId(), cslt.getPart_Number(), "View" ) ;
                    shippedPartNo = new String[ subPartNoList.size() ];
                    subPartNoList.toArray( shippedPartNo );

                    cslt.setShipped_Part_Number ( shippedPartNo );
                    cslt.setSubstitutablePartNumbers ( loadSubstitutablePartNumbersByCsLineItemId  ( null , cslt.getCustomerShippingLineItemId(), cslt.getPart_Number(), "View", cslt.getProduct_ID() ) );
                    cslt.setQtyPulledSubstitutablePartNo( getShippedPartNumbersByCsLineItemId  ( null , cslt.getCustomerShippingLineItemId(), cslt.getPart_Number(), "View" ) );

                    cslt.setParentPartNumber( rset.getString( "Parent_Part_Number" ) );
                    cslt.setCustomer_Label_Number( rset.getString( "Customer_Label_Number" ) );
                    cslt.setOdm_Part_Number( rset.getString( "ODM_Part_Number" ) );
                    /*if( rset.getInt("Carton_Number") > 0 )
                    {
                        cslt.setCartonNumber( rset.getString("Carton_Number") );
                    }*/
                    //System.out.println("------------------PS-DAO-LOADPS- CS-LI-ID = " + cslt.getCustomerShippingLineItemId() + " Part_Number = " + cslt.getPart_Number());
                    lineItemList.add( cslt );
                }
                shippingAdviseForm.setLineitems( lineItemList );
            }

            //shippingAdviseForm.setCountryOfFabOrigin ( CommercialInvoiceDAO.getFabAssmCountryOfOrigin( customerShippingId, "FAB" ));
            //shippingAdviseForm.setCountryOfAssyOrigin( CommercialInvoiceDAO.getFabAssmCountryOfOrigin( customerShippingId, "ASSM" ) );

            shippingAdviseForm.setCountryOfFabOrigin( CommercialInvoiceDAO.loadCountryOfOrigin( null, customerShippingId, Constants.LOCATION_TYPE_FAB ) );
            shippingAdviseForm.setCountryOfAssyOrigin( CommercialInvoiceDAO.loadCountryOfOrigin( null, customerShippingId, Constants.LOCATION_TYPE_ASSEMBLY ) );
        }
        catch( Throwable t )
        {
            t.printStackTrace();
            throw new ScalarException( "Could not fetch the Shipping Advice details from database", t );
        }
        finally
        {
            if( rset != null )
            {
                try { rset.close(); } catch( SQLException excp ) {}
            }
            if( pstmt != null )
            {
                try { pstmt.close(); } catch( SQLException excp ) {}
            }
            if( conn != null )
            {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return shippingAdviseForm;
    }

    public static void updateShippingAdvise( DBTransaction dbTransaction, ShippingAdviseForm shippingAdviseForm, HashMap finalInventoryMap ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success     = false;
        double dWeight      = 0.0;
        double grossWeight  = 0.0;

        if( shippingAdviseForm == null ) return;

        try
        {
            try
            {
                dWeight = Double.parseDouble( shippingAdviseForm.getWeight() );
            }
            catch( Exception e ) { dWeight = 0.0; }

            try
            {
                grossWeight = Double.parseDouble( shippingAdviseForm.getGrossWeight() );
            }
            catch( Exception e ) { grossWeight = 0.0; }

            HashMap InventoryDataMapDB = getFinalInventoryMapByLineItemId( NewPackingSlipDAO.loadLotNumberDataByCustomerShippingId ( shippingAdviseForm.getCustomerShippingId() ) );
            mergeDBInventoryDataMapIntoExistMap( finalInventoryMap , InventoryDataMapDB );

            // Setting the LotIDs for the New Lots entered through Drop Shipment
            if( shippingAdviseForm.isDropShippment() && ! shippingAdviseForm.getStatus().equalsIgnoreCase( "shipped" ) )
            {
                fetchLotIdForNewLots( dbTransaction, finalInventoryMap );
            }

            if( shippingAdviseForm.getEtd() != null && shippingAdviseForm.getEtd().trim().length() == 0 )
            {
                shippingAdviseForm.setEtd( null );
            }
            if( shippingAdviseForm.getEta() != null && shippingAdviseForm.getEta().trim().length() == 0 )
            {
                shippingAdviseForm.setEta( null );
            }

            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( SqlQueryMappings.get ( "operations.packingslip.vendorInterface.updateShippingAdviceDetails" ) );
            pstmt.setString( 1,  shippingAdviseForm.getFlightNumberOne() );
            pstmt.setString( 2,  shippingAdviseForm.getFlightRoute() );
            pstmt.setString( 3,  shippingAdviseForm.getEtd() );
            pstmt.setString( 4,  shippingAdviseForm.getEta() );
            pstmt.setString( 5,  shippingAdviseForm.getHawb() );
            pstmt.setString( 6,  shippingAdviseForm.getMawb() );
            pstmt.setString( 7,  shippingAdviseForm.getShippingMethod() );
            pstmt.setString( 8,  shippingAdviseForm.getDeliveryTerms() );
            pstmt.setString( 9, null);
            pstmt.setString( 10, shippingAdviseForm.getCartonCount() );
            pstmt.setDouble( 11, dWeight);
            pstmt.setString( 12, shippingAdviseForm.getTrackingAsString() );
            pstmt.setString( 13, shippingAdviseForm.getPaletteCount() );
            pstmt.setDate  ( 14, StringUtils.getSQLDate( shippingAdviseForm.getShipDate(), null ) );
            pstmt.setString( 15, shippingAdviseForm.getShippingInstructions() );
            pstmt.setString( 16, shippingAdviseForm.getComments() );
            pstmt.setString( 17, shippingAdviseForm.getPackedby() );
            pstmt.setString( 18, shippingAdviseForm.getTitle() );
            pstmt.setString( 19, shippingAdviseForm.getFreightInstructions() );
            pstmt.setDouble( 20, grossWeight);
            pstmt.setString( 21, shippingAdviseForm.getWeightType() );
            pstmt.setString( 22, shippingAdviseForm.getPackageDimension() );
            pstmt.setString( 23, shippingAdviseForm.getUomPkgDimension() );
            pstmt.setString( 24, shippingAdviseForm.getFlightNumberTwo() );
            pstmt.setInt   ( 25, shippingAdviseForm.getCustomerShippingId() );
            pstmt.execute();
            pstmt.close();

            /*pstmt = conn.prepareStatement( "UPDATE CUSTOMER_SHIPPING_LOT_NUMBER_TABLE SET TRACKING_NUMBER = ? WHERE CSLIT_ID = ? AND LOT_ID = ?" );
            HashMap pulledLotMap = shippingAdviseForm.getPulledLotMap();
            CustomerShippingLotNumberData pulledLot = null;

            if( ( pulledLotMap != null ) && ( pulledLotMap.isEmpty() == false ) )
            {
                Iterator pulledLotListIterator = pulledLotMap.values().iterator();
                ArrayList lotList = null;

                while( pulledLotListIterator.hasNext() == true )
                {
                    lotList = ( ArrayList ) pulledLotListIterator.next();

                    if( ( lotList != null ) && ( lotList.isEmpty() == false ) )
                    {
                        int size = lotList.size();
                        for( int i=0; i<size; i++ )
                        {
                            pulledLot = ( CustomerShippingLotNumberData ) lotList.get(i);
                            pstmt.setString( 1, pulledLot.getTrackingNumber() );
                            pstmt.setInt( 2, pulledLot.getId() );
                            pstmt.setInt( 3, pulledLot.getLotId() );

                            pstmt.addBatch();
                        }

                        pstmt.executeBatch();
                        pstmt.clearParameters();
                    }
                }
            } */

            if( ! shippingAdviseForm.getStatus().equalsIgnoreCase( "shipped" ) )
            {
                // Step 1
                // Update into Customer_shipping_line_Item table
                pstmt = conn.prepareStatement (  SqlQueryMappings.get( "operations.packingslip.updateLineItems" ) );

                CustomerShippingLineItems lineItem = null;
                int lineItemCount 	= 0;
                //int cartonNumber    = 0;
                String shippedPartNumbers = null;

                ArrayList lineItems = shippingAdviseForm.getLineitems();
                if( lineItems != null && lineItems.isEmpty() == false )
                {
                    lineItemCount = lineItems.size();
                }

                for ( int i = 0; i < lineItemCount; i++ )
                {
                    lineItem = ( CustomerShippingLineItems ) lineItems.get (i);
                    if( lineItem.getQtyPulled() > 0 )
                    {
                        pstmt.setInt 	( 1, lineItem.getQtyPulled() );
                        pstmt.setDate   ( 2, StringUtils.getSQLDate( shippingAdviseForm.getShipDate(), null ) );
                        pstmt.setString ( 3, lineItem.getCool_Description() );
                        pstmt.setString ( 4, lineItem.getUnits() );
                        pstmt.setString ( 5, lineItem.getCustomer_Part_Number() );

                        shippedPartNumbers = getShippedPartNumberString( lineItem.getShipped_Part_Number(), null );
                        if( shippedPartNumbers != null && shippedPartNumbers.trim().length() > 0 && ! shippedPartNumbers.equals( lineItem.getPart_Number() ) )
                        {
                            pstmt.setString	( 6, shippedPartNumbers );
                        }
                        else
                        {
                            pstmt.setString	( 6, null );
                        }
                        pstmt.setString ( 7, lineItem.getCustomer_Label_Number() );
                        //pstmt.setInt    ( 8, cartonNumber );
                        pstmt.setInt 	( 8, lineItem.getCustomerShippingLineItemId() );

                        //System.out.println("-----------------PS-DAO-updateShippingAdvise--CS-LineItem = " + lineItem.getPart_Number() + " Qty = " + lineItem.getQtyPulled());
                        pstmt.addBatch();
                    }
                }
                pstmt.executeBatch();
                pstmt.close();
                pstmt = null;

                // Step 2: Update 'Balance' and 'Shipping' Fields in Inventory_Lot Table

                int finalInventoryDataListSize = -1;
                ArrayList finalInventoryDataList = null;
                FinalInventoryData inventoryData = null;

                if( finalInventoryMap != null )
                {
                    pstmt = conn.prepareStatement ( SqlQueryMappings.get( "operations.packingslip.updateInventoryLotData" ) );

                    for( int i=0; i < lineItemCount; i++ )
                    {
                        finalInventoryDataList = ( ArrayList ) finalInventoryMap.get ( ( ( CustomerShippingLineItems ) lineItems.get(i) ).getLineItemId()+"" );
                        if( finalInventoryDataList != null )
                        {
                            finalInventoryDataListSize = finalInventoryDataList.size();
                            for( int j = 0; j < finalInventoryDataListSize; j++ )
                            {
                                inventoryData = (FinalInventoryData) finalInventoryDataList.get ( j );
                                pstmt.setInt( 1, inventoryData.getLotId() );
                                pstmt.setInt( 2, inventoryData.getBalance() );
                                pstmt.setInt( 3, inventoryData.getCsLineItemId() );
                                pstmt.setInt( 4, inventoryData.getQuantityPull() );
                                pstmt.setInt( 5, inventoryData.getIsReduceBalance() );
                                pstmt.setInt( 6, 1 );
                                pstmt.setDate( 7, StringUtils.getSQLDate( shippingAdviseForm.getShipDate(), null ) );
                                pstmt.setInt   ( 8, 0); // '0' is set when vendor is updating the Inventory else set '1'.
                                pstmt.setString(9 , inventoryData.getDateCode() );
                                //System.out.println("------------PS DAO--EU-inventoryData --- " + inventoryData);
                                pstmt.addBatch();
                            }
                            pstmt.executeBatch();
                            pstmt.clearBatch();
                        }
                    }
                    pstmt.close();
                    pstmt = null;
                }

                // Step 4:Update into Customer_Shiping_Lot_Number Table

                if( finalInventoryMap != null && finalInventoryMap.isEmpty() == false )
                {
                    pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.packingslip.saveCustomerShippingLotNumberInfo" ) );
                    for( int i=0; i < lineItemCount; i++ )
                    {
                        finalInventoryDataList = ( ArrayList ) finalInventoryMap.get ( ( ( CustomerShippingLineItems ) lineItems.get(i) ).getLineItemId()+"" );

                        if( finalInventoryDataList != null )
                        {
                            finalInventoryDataListSize = finalInventoryDataList.size();

                            for( int j = 0; j < finalInventoryDataListSize; j++ )
                            {
                                inventoryData = (FinalInventoryData) finalInventoryDataList.get ( j ) ;
                                pstmt.setInt( 1, inventoryData.getCsLineItemId() );
                                pstmt.setInt( 2, inventoryData.getLotId() );
                                pstmt.setInt( 3, inventoryData.getQuantityPull() );
                                pstmt.addBatch();
                            }
                            pstmt.executeBatch();
                            pstmt.clearBatch();
                        }
                    }

                    pstmt.close();
                    pstmt = null;
                }
            }
            success = true;
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not update Shipping Advise details", sqle);
        }
        finally
        {
            if( dbTransaction == null )
            {
                try
                {
                    if( conn != null )
                    {
                        if( success == true )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch( SQLException ignore ) {}
            }

            if( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }

            if( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
    }


    public static void confirmShippingAdvise( ShippingAdviseForm shippingAdviseForm, HashMap finalInventoryMap, String userId ) throws ScalarException
    {
        DBTransaction dbTransaction  = null;
        Connection conn             = null;
        PreparedStatement pstmt     = null;
        CallableStatement cstmt     = null;
        boolean success             = false;
        boolean isInvoiceNoExists   = true;
        String invoiceNo            = "";

        if( shippingAdviseForm == null ) return;

        try
        {
            HashMap InventoryDataMapDB = getFinalInventoryMapByLineItemId( NewPackingSlipDAO.loadLotNumberDataByCustomerShippingId ( shippingAdviseForm.getCustomerShippingId() ) );
            mergeDBInventoryDataMapIntoExistMap( finalInventoryMap , InventoryDataMapDB );

            dbTransaction = DBTransaction.getInstance();
            if( dbTransaction != null )
            {
                dbTransaction.begin();
            }
            if ( shippingAdviseForm.getInvoiceNumber() == null || shippingAdviseForm.getInvoiceNumber().trim().length() == 0 )
            {
                //shippingAdviseForm.setInvoiceNumber( PackingSlipDAO.getNextPsInvoiceNumber( dbTransaction ) );
                invoiceNo = PackingSlipDAO.getNextPsInvoiceNumber( dbTransaction );
                while( isInvoiceNoExists )
                {
                    isInvoiceNoExists = isInvoiceNoExists( dbTransaction, invoiceNo );
                    if( isInvoiceNoExists )
                    {
                        invoiceNo = generateNextUniqueInvoiceNo( invoiceNo );
                    }
                }
                shippingAdviseForm.setInvNo( invoiceNo );
            }

            if( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            // Setting the LotIDs for the New Lots entered through Drop Shipment
            if( shippingAdviseForm.isDropShippment() && ! shippingAdviseForm.getStatus().equalsIgnoreCase( "shipped" ) )
            {
                fetchLotIdForNewLots( dbTransaction, finalInventoryMap );
            }

            if( shippingAdviseForm.getEtd() != null && shippingAdviseForm.getEtd().trim().length() == 0 )
            {
                shippingAdviseForm.setEtd( null );
            }
            if( shippingAdviseForm.getEta() != null && shippingAdviseForm.getEta().trim().length() == 0 )
            {
                shippingAdviseForm.setEta( null );
            }

            // Step 1
            // Update into Customer_shipping_line_Item table
            //pstmt = conn.prepareStatement ( "UPDATE Customer_Shipping_Line_Item_Table SET CSLIT_Qty_Shipped = ?, CSLIT_Shipped_Date = ?,CSLIT_Description=?,CSLIT_Units=?, CSLIT_Cust_Part_Number = ? WHERE CSLIT_ID = ?" );
            pstmt = conn.prepareStatement (  SqlQueryMappings.get( "operations.packingslip.updateLineItems" ) );

            CustomerShippingLineItems lineItem = null;
            int lineItemCount 	= 0;
            //int cartonNumber    = 0;
            String shippedPartNumbers = null;

            ArrayList lineItems = shippingAdviseForm.getLineitems();
            if( lineItems != null && lineItems.isEmpty() == false )
            {
                lineItemCount = lineItems.size();
            }

            for ( int i = 0; i < lineItemCount; i++ )
            {
                lineItem = ( CustomerShippingLineItems ) lineItems.get (i);
                /*try{
                    cartonNumber = Integer.parseInt( lineItem.getCartonNumber() );
                }catch(NumberFormatException nfe){}*/
                if( lineItem.getQtyPulled() > 0 )
                {
                    pstmt.setInt 	( 1, lineItem.getQtyPulled() );
                    pstmt.setDate   ( 2, StringUtils.getSQLDate( shippingAdviseForm.getShipDate(), null ) );
                    pstmt.setString ( 3, lineItem.getCool_Description() );
                    pstmt.setString ( 4, lineItem.getUnits() );
                    pstmt.setString ( 5, lineItem.getCustomer_Part_Number() );
                    shippedPartNumbers = getShippedPartNumberString( lineItem.getShipped_Part_Number(), null );
                    if( shippedPartNumbers != null && shippedPartNumbers.trim().length() > 0 && ! shippedPartNumbers.equals( lineItem.getPart_Number() ) )
                    {
                        pstmt.setString	( 6, shippedPartNumbers );
                    }
                    else
                    {
                        pstmt.setString	( 6, null );
                    }
                    pstmt.setString ( 7, lineItem.getCustomer_Label_Number() );//Added by Krishna Prasad on 23-11-2004
                    //pstmt.setInt    ( 8, cartonNumber );
                    pstmt.setInt 	( 8, lineItem.getCustomerShippingLineItemId() );
                    //System.out.println("-----------------PS-DAO-updateShippingAdvise--CS-LineItem = " + lineItem.getPart_Number() + " Qty = " + lineItem.getQtyPulled());
                    pstmt.addBatch();
                }
            }
            pstmt.executeBatch();
            pstmt.close();
            pstmt = null;

            // Step 2
            // Update 'Balance' and 'Shipping' Fields in Inventory_Lot Table
            int finalInventoryDataListSize = -1;
            ArrayList finalInventoryDataList = null;
            FinalInventoryData inventoryData = null;

            if( finalInventoryMap != null )
            {
                //pstmt = conn.prepareStatement ( "{call sp_update_balance_qty_in_inventory_lot (?, ?, ?, ?) }" );
                pstmt = conn.prepareStatement ( SqlQueryMappings.get( "operations.packingslip.updateInventoryLotData" ) );
                for( int i=0; i < lineItemCount; i++ )
                {
                    finalInventoryDataList = ( ArrayList ) finalInventoryMap.get ( ( ( CustomerShippingLineItems ) lineItems.get(i) ).getLineItemId()+"" );
                    if( finalInventoryDataList != null )
                    {
                        finalInventoryDataListSize = finalInventoryDataList.size();
                        for( int j = 0; j < finalInventoryDataListSize; j++ )
                        {
                            inventoryData = (FinalInventoryData) finalInventoryDataList.get ( j );
                            pstmt.setInt( 1, inventoryData.getLotId() );
                            pstmt.setInt( 2, inventoryData.getBalance() );
                            pstmt.setInt( 3, inventoryData.getCsLineItemId() );
                            pstmt.setInt( 4, inventoryData.getQuantityPull() );
                            pstmt.setInt( 5, 0 );
                            pstmt.setInt( 6, 0 );
                            pstmt.setDate( 7, StringUtils.getSQLDate( shippingAdviseForm.getShipDate(), null ) );
                            pstmt.setInt   ( 8, 1); // '0' is set when vendor is updating lineitems inventory in issue stage else set '1'.
                            pstmt.setString(9, inventoryData.getDateCode() );
                            //System.out.println("------------PS DAO--EU-inventoryData --- " + inventoryData);
                            pstmt.addBatch();
                        }
                        pstmt.executeBatch();
                        pstmt.clearBatch();
                    }
                }
                pstmt.close();
                pstmt = null;
            }

            // Step 4
            // Update into Customer_Shiping_Lot_Number Table
            if( finalInventoryMap != null && finalInventoryMap.isEmpty() == false )
            {
                //pstmt = conn.prepareStatement( "{ call sp_save_customer_shipping_lot_number_data( ?, ?, ? ) }" );
                pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.packingslip.saveCustomerShippingLotNumberInfo" ) );
                for( int i=0; i < lineItemCount; i++ )
                {
                    finalInventoryDataList = ( ArrayList ) finalInventoryMap.get ( ( ( CustomerShippingLineItems ) lineItems.get(i) ).getLineItemId()+"" );
                    if( finalInventoryDataList != null )
                    {
                        finalInventoryDataListSize = finalInventoryDataList.size();
                        for( int j = 0; j < finalInventoryDataListSize; j++ )
                        {
                            inventoryData = (FinalInventoryData) finalInventoryDataList.get ( j ) ;
                            pstmt.setInt( 1, inventoryData.getCsLineItemId() );
                            pstmt.setInt( 2, inventoryData.getLotId() );
                            pstmt.setInt( 3, inventoryData.getQuantityPull() );
                            pstmt.addBatch();
                        }
                        pstmt.executeBatch();
                        pstmt.clearBatch();
                    }
                }

                pstmt.close();
                pstmt = null;
            }

            // Step 4.5
            // Update Backlog_Master with the Quantity Shipped
            cstmt = conn.prepareCall ( SqlQueryMappings.get ( "operations.packingslip.updateBacklogMaster" ) );
            cstmt.setInt ( 1, shippingAdviseForm.getCustomerShippingId() );
            cstmt.executeUpdate();

            cstmt.close();
            cstmt = null;

            double dWeight      = 0.0;
            double grossWeight  = 0.0;
            try
            {
                dWeight = Double.parseDouble( shippingAdviseForm.getWeight() );
            }
            catch( Exception e ) { dWeight = 0.0; }
            try
            {
                grossWeight = Double.parseDouble( shippingAdviseForm.getGrossWeight() );
            }
            catch( Exception e ) { grossWeight = 0.0; }

            //pstmt = conn.prepareStatement( "{call sp_update_vendor_packing_slip_status (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }" );
            pstmt = conn.prepareStatement( SqlQueryMappings.get ( "operations.packingslip.vendorInterface.updatePackingSlipStatus" ) );
            pstmt.setString( 1,  shippingAdviseForm.getFlightNumberOne() );
            pstmt.setString( 2,  shippingAdviseForm.getFlightRoute() );
            pstmt.setString( 3,  shippingAdviseForm.getEtd() );
            pstmt.setString( 4,  shippingAdviseForm.getEta() );
            pstmt.setString( 5,  shippingAdviseForm.getHawb() );
            pstmt.setString( 6,  shippingAdviseForm.getMawb() );
            pstmt.setString( 7,  shippingAdviseForm.getShippingMethod() );
            pstmt.setString( 8,  shippingAdviseForm.getDeliveryTerms() );
            pstmt.setString( 9, shippingAdviseForm.getCartonCount() );
            pstmt.setDouble( 10, dWeight );
            pstmt.setString( 11, shippingAdviseForm.getTrackingAsString() );
            pstmt.setString( 12, shippingAdviseForm.getPaletteCount() );
            pstmt.setDate  ( 13, StringUtils.getSQLDate( shippingAdviseForm.getShipDate(), null ) );
            pstmt.setString( 14, shippingAdviseForm.getShippingInstructions() );
            pstmt.setString( 15, shippingAdviseForm.getComments() );
            pstmt.setString( 16, shippingAdviseForm.getPackedby() );
            pstmt.setString( 17, shippingAdviseForm.getTitle() );
            pstmt.setString( 18, shippingAdviseForm.getFreightInstructions() );
            pstmt.setString( 19, CUSTOMER_SHIPPING_STATUS_SHIPPED );
            pstmt.setInt   ( 20, shippingAdviseForm.getCustomerShippingId() );
            pstmt.setString( 21, shippingAdviseForm.getInvoiceNumber() );
            pstmt.setDouble( 22, grossWeight );
            pstmt.setString( 23, shippingAdviseForm.getWeightType() );
            pstmt.setString( 24, shippingAdviseForm.getPackageDimension() );
            pstmt.setString( 25, shippingAdviseForm.getUomPkgDimension() );
            pstmt.setString( 26, shippingAdviseForm.getFlightNumberTwo() );
            pstmt.execute();
            pstmt.close();

            // Update status in Customer_Open_Order_Line_Item table
            cstmt = conn.prepareCall ( SqlQueryMappings.get ( "operations.packingslip.updateOpenOrderLineItemStatus" ) );
            cstmt.setInt 	( 1, shippingAdviseForm.getCustomerShippingId() );
            cstmt.setString ( 2, CUSTOMER_SHIPPING_STATUS_SHIPPED );
            cstmt.setString ( 3, userId );
            cstmt.executeUpdate();
            cstmt.close();
            cstmt = null;
            if ( shippingAdviseForm.isConsignment() )
            {
                cstmt = conn.prepareCall( "{ CALL SP_MOVE_INVENTORY_TO_CONSIGNMENT_LOCATION( ?, ? ) }" );
                cstmt.setInt( 1, shippingAdviseForm.getCustomerShippingId() );
                cstmt.registerOutParameter( 2, java.sql.Types.VARCHAR );
                cstmt.executeUpdate();

                String errorMsg = cstmt.getString(2);
                if ( errorMsg != null && errorMsg.trim().length() > 0 )
                {
                    throw new ScalarException ( errorMsg );
                }
            }
            success = true;
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( sqle.getMessage(), sqle);
        }
        finally
        {
            try
            {
                if( dbTransaction == null )
                {
                    if ( conn != null )
                    {
                        if (success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                else
                {
                    if (success == true)
                    {
                        dbTransaction.commit();
                    }
                    else
                    {
                        dbTransaction.rollback();
                    }
                }
             }
             catch (SQLException ignore) {}

            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
             if (cstmt != null)
             {
                 try { cstmt.close(); } catch (SQLException ignore){}
             }
             if (conn != null)
             {
                 try { conn.close(); } catch (SQLException ignore){}
             }
        }


    }


    // BR, Mar 13, 2004
    public static String[] loadCustomerShipAlertEmails( String customerName ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        String emails = null;

        try {
            conn = DataSource.getConnection();

            //pstmt = conn.prepareStatement( "SELECT EMAILS FROM CUSTOMER_SHIP_ALERT_EMAILS_TABLE WHERE CUSTOMER_NAME = ?" );
            pstmt = conn.prepareStatement( SqlQueryMappings.get("operations.packingslip.loadCustomerShipAlertEmails") );
            pstmt.setString( 1, customerName );

            rset = pstmt.executeQuery();

            if( rset.next() )
            {
                emails = rset.getString( "EMAILS" );
            }
        }
        catch( Exception e )
        {
            throw new ScalarException( "Could not retrieve Customer Ship Alert Email list from database", e );
        }
        finally
        {
            if( rset != null )
            {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        String[] emailArray = StringUtils.toStringArray( emails, Mailer.EMAIL_SEPARATOR );

        if( ( emailArray != null ) && ( emailArray.length > 0 ) )
        {
            Arrays.sort( emailArray );
        }

        return emailArray;
    }

    // BR, Mar 13, 2004
    public static String[] loadClientEmailsForShipAlert() throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        String emails = null;

        try {
            conn = DataSource.getConnection();

            //pstmt = conn.prepareStatement( "select Ship_Alert_Emails from notification_emails_table" );
            pstmt = conn.prepareStatement( SqlQueryMappings.get ( "operations.packingslip.vendorInterface.loadClientEmailsForShipAlert" ) );
            rset = pstmt.executeQuery();

            if( rset.next() )
            {
                emails = rset.getString( "Ship_Alert_Emails" );
            }
        }
        catch( Exception e )
        {
            throw new ScalarException( "Could not retrieve Ship Alert Email list from database", e );
        }
        finally
        {
            if( rset != null )
            {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        String[] emailArray = StringUtils.toStringArray( emails, Mailer.EMAIL_SEPARATOR );

        if( ( emailArray != null ) && ( emailArray.length > 0 ) )
        {
            Arrays.sort( emailArray );
        }

        return emailArray;
    }

    public static String[] loadAllShipAlertEmails ( int csId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        String emails = "";

        try
        {

            conn = DataSource.getConnection();

            //step-1
            //SELECT Contact_Person, Email FROM Customer_Contact_Persons_Table WHERE  (Email IS NOT NULL) AND (Email <> '') AND (Deactivated <> 1) AND ( Ship_Alerts = 0 ) AND (Customer_Id = ? )
            //pstmt = conn.prepareStatement( "SELECT Contact_Person, Email FROM fn_load_all_ship_alert_emails ( ? ) " );
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.packingslip.loadAllShipAlertEmails" ) );

            pstmt.setInt( 1, csId );
            rset = pstmt.executeQuery();

            while ( rset.next() )
            {
                emails = emails + rset.getString( "Email" ) + ",";
            }

            if( emails != null && emails.length() > 0 )
            {
                emails =  emails.substring( 0 , emails.length() - 1 );
            }
            emails = StringUtils.removeDuplicateEmailIds(emails);
        }
        catch( Exception e )
        {
            throw new ScalarException( "Could not retrieve Ship Alert Email list from database", e );
        }
        finally
        {
            if( rset != null )
            {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        String[] emailArray = StringUtils.toStringArray( emails, Mailer.EMAIL_SEPARATOR );

        if( ( emailArray != null ) && ( emailArray.length > 0 ) )
        {
            Arrays.sort( emailArray );
        }

        return emailArray;
    }

    public static String loadAllPackingSlipEmails ( int customerId, int cstId, int odmId, boolean isVendor ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        String emails = "";

        try
        {
            conn = DataSource.getConnection();
            if( isVendor )
            {
                //pstmt = conn.prepareStatement( "SELECT * FROM fn_load_all_packing_slip_emails ( ?, ?, ?, ?, ? ) Where DefaultChecked = 0 AND ( Source = 'USER' OR Source = 'NOTIFICATION' ) " );
                pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.packingslip.loadVendorPackingSlipEmails" ) );
            }
            else
            {
                //pstmt = conn.prepareStatement( "SELECT * FROM fn_load_all_packing_slip_emails ( ?, ?, ?, ?, ? ) Where DefaultChecked = 0 " );
                pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.packingslip.loadAllPackingSlipEmails" ) );
            }

            pstmt.setInt( 1, customerId );
            pstmt.setInt( 2, cstId );
            pstmt.setInt( 3, 0 );
            pstmt.setInt( 4, 0 );
            pstmt.setInt( 5, 0 );
            rset = pstmt.executeQuery();

            while ( rset.next() )
            {
                emails = emails + rset.getString( "Email" ) + ",";
            }

            if( isVendor == false )
            {
                pstmt.setInt( 1, odmId );
                pstmt.setInt( 2, -1 );
                pstmt.setInt( 3, 1 );
                pstmt.setInt( 4, 1 );
                pstmt.setInt( 5, 1 );
                rset = pstmt.executeQuery();

                while ( rset.next() )
                {
                    emails = emails + rset.getString( "Email" ) + ",";
                }
            }
            if( emails != null && emails.length() > 0 )
            {
                emails =  emails.substring( 0 , emails.length() - 1 );
            }

            emails = StringUtils.removeDuplicateEmailIds(emails);
        }
        catch( Exception e )
        {
            throw new ScalarException( "Could not retrieve Ship Alert Email list from database", e );
        }
        finally
        {
            if( rset != null )
            {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        /*String[] emailArray = StringUtils.toStringArray( emails, Mailer.EMAIL_SEPARATOR );

        if( ( emailArray != null ) && ( emailArray.length > 0 ) )
        {
            Arrays.sort( emailArray );
        } */

        return emails;
    }

    public static ArrayList loadAllPackingSlipEmailsList ( int customerId, int cstId, int odmId, boolean isVendor ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList emailList = new ArrayList();
        String contactPersonType = "";
        String contactPersonString = "";
        String email = "";

        try
        {
            conn = DataSource.getConnection();
            if( isVendor )
            {
                //pstmt = conn.prepareStatement( "SELECT * FROM fn_load_all_packing_slip_emails ( ?, ?, ?, ?, ? ) Where DefaultChecked = 0 AND ( Source = 'USER' OR Source = 'NOTIFICATION' ) " );
                pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.packingslip.loadVendorPackingSlipEmails" ) );
            }
            else
            {
                //pstmt = conn.prepareStatement( "SELECT * FROM fn_load_all_packing_slip_emails ( ?, ?, ?, ?, ? ) Where DefaultChecked = 0 " );
                pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.packingslip.loadAllPackingSlipEmails" ) );
            }

            pstmt.setInt( 1, customerId );
            pstmt.setInt( 2, cstId );
            pstmt.setInt( 3, 0 );
            pstmt.setInt( 4, 0 );
            pstmt.setInt( 5, 0 );
            rset = pstmt.executeQuery();

            while ( rset.next() )
            {

                contactPersonType = rset.getString( "Source" );
                email = rset.getString( "Email" );
                contactPersonString = rset.getString( "Contact_Person" );
                if( contactPersonType != null && contactPersonType.trim().length() > 0 && contactPersonType.startsWith("CUSTOMER") )
                {
                    ContactPerson contactPerson = new ContactPerson();
                    contactPerson.setContactPerson( contactPersonString );
                    contactPerson.setEmail( email );
                    contactPerson.setContactPersonType( "CONTACT PERSON" );
                    contactPerson.setIsChecked( true );
                    emailList.add( contactPerson );
                }
                else if( contactPersonType != null && contactPersonType.trim().length() > 0 && contactPersonType.equals("VENDOR_USER") )
                {
                    ContactPerson contactPerson = new ContactPerson();
                    contactPerson.setContactPerson( contactPersonString );
                    contactPerson.setEmail( email );
                    contactPerson.setContactPersonType( "EXTERNAL USER" );
                    contactPerson.setIsChecked( true );
                    emailList.add( contactPerson );
                }
                else if( contactPersonType != null && contactPersonType.trim().length() > 0 && contactPersonType.equals("NOTIFICATION") )
                {
                    String[] emailsList = email.split(",");
                    if( emailsList != null && emailsList.length > 0 )
                    {
                        for( int j = 0; j < emailsList.length; j++)
                        {
                            if( emailsList[j] != null && emailsList[j].trim().length() > 0 )
                            {
                                ContactPerson contactPerson = new ContactPerson();
                                contactPerson.setContactPerson( contactPersonString );
                                contactPerson.setEmail( emailsList[j] );
                                contactPerson.setContactPersonType( "NOTIFICATION" );
                                contactPerson.setIsChecked( true );
                                emailList.add( contactPerson );
                            }
                        }
                    }
                }
                else
                {
                    ContactPerson contactPerson = new ContactPerson();
                    contactPerson.setContactPerson( contactPersonString );
                    contactPerson.setEmail( email );
                    contactPerson.setContactPersonType( "USER" );
                    contactPerson.setIsChecked( true );
                    emailList.add( contactPerson );
                }

            }

            if( isVendor == false )
            {
                pstmt.setInt( 1, odmId );
                pstmt.setInt( 2, -1 );
                pstmt.setInt( 3, 1 );
                pstmt.setInt( 4, 1 );
                pstmt.setInt( 5, 1 );
                rset = pstmt.executeQuery();

                while ( rset.next() )
                {
                    contactPersonType = rset.getString( "Source" );
                    email = rset.getString( "Email" );
                    contactPersonString = rset.getString( "Contact_Person" );
                    if( contactPersonType != null && contactPersonType.trim().length() > 0 && contactPersonType.startsWith("CUSTOMER") )
                    {
                        ContactPerson contactPerson = new ContactPerson();
                        contactPerson.setContactPerson( contactPersonString );
                        contactPerson.setEmail( email );
                        contactPerson.setContactPersonType( "CONTACT PERSON" );
                        contactPerson.setIsChecked( true );
                        emailList.add( contactPerson );
                    }
                    else
                    {
                        ContactPerson contactPerson = new ContactPerson();
                        contactPerson.setContactPerson( contactPersonString );
                        contactPerson.setEmail( email );
                        contactPerson.setContactPersonType( "USER" );
                        contactPerson.setIsChecked( true );
                        emailList.add( contactPerson );
                    }
                }
            }
        }
        catch( Exception e )
        {
            throw new ScalarException( "Could not retrieve Ship Alert Email list from database", e );
        }
        finally
        {
            if( rset != null )
            {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        /*String[] emailArray = StringUtils.toStringArray( emails, Mailer.EMAIL_SEPARATOR );

        if( ( emailArray != null ) && ( emailArray.length > 0 ) )
        {
            Arrays.sort( emailArray );
        } */

        return emailList;
    }


    // BR, March 16, 2004
    public static boolean updateLotTrackingNumber( DBTransaction dbTransaction, String cslitId, String lotId, String trackingNumber )
        throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        boolean rowsUpdated = false;

        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            //pstmt = conn.prepareStatement( "UPDATE CUSTOMER_SHIPPING_LOT_NUMBER_TABLE SET TRACKING_NUMBER = ? WHERE CSLIT_ID = ? AND LOT_ID = ?" );
            pstmt = conn.prepareStatement( SqlQueryMappings.get("operations.packingslip.vendorInterface.updateLotTrackingNumber") );
            pstmt.setString( 1, trackingNumber );
            pstmt.setString( 2, cslitId );
            pstmt.setString( 3, lotId );

            rowsUpdated = ( pstmt.executeUpdate() > 0 );

            success = true;
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not update Shipping Advise details", sqle);
        }
        finally
        {
            if( dbTransaction == null )
            {
                try
                {
                    if( conn != null )
                    {
                        if( success == true )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch( SQLException ignore ) {}
            }

            if( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }

            if( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return rowsUpdated;
    }

    /**
     * @param finalInventoryMap
     */
    public static void fetchLotIdForNewLots( DBTransaction dbTransaction, HashMap finalInventoryMap ) throws ScalarException
    {
        int finalInventoryDataListSize 		= -1;
        Iterator finalInventoryMapIterator 	= null;
        ArrayList finalInventoryDataList 	= null;
        FinalInventoryData inventoryData 	= null;
        //ArrayList inventoryLotList 			= new ArrayList(); // This contains the list of all New Lots
        InventoryLot inventoryLot 			= null;			   // This contains the Individual Lot Details
        //String vendorLocation				= null;
        //int vendorLocationId				= -1;
        //int lotId							= -1;
        //String lotNumber					= null;
        //String partNumber 					= null;

        try
        {
            if( finalInventoryMap != null )
            {
                finalInventoryMapIterator 	= finalInventoryMap.keySet().iterator();
                while( finalInventoryMapIterator.hasNext() )
                {
                    finalInventoryDataList 		= ( ArrayList )finalInventoryMap.get( finalInventoryMapIterator.next() );
                    if( finalInventoryDataList != null )
                    {
                        finalInventoryDataListSize = finalInventoryDataList.size();
                        for( int j = 0; j < finalInventoryDataListSize; j++ )
                        {
                            inventoryData = (FinalInventoryData) finalInventoryDataList.get ( j ) ;
                            if( inventoryData.getLotId() == -1 )
                            {
                                //Location location = VendorDAO.getLocationByLocationID( inventoryData.getLocationId() );
                                String location = VendorDAO.getLocationByAddressId( inventoryData.getLocationId() , Constants.LOCATION_TYPE_FINISHED_GOODS );
                                if( location.indexOf('/') != -1 )
                                    location = location.substring( 0, location.indexOf('/') );
                                if( location != null )
                                {

                                    inventoryLot = new InventoryLot ( location , Constants.LOCATION_TYPE_FINISHED_GOODS, inventoryData.getLotNumber(), inventoryData.getMktPartNumber(), StringUtils.formatDateToString( inventoryData.getDateIn() ) , inventoryData.getQuantityIn() +"", inventoryData.getDateCode() );
                                    inventoryLot.setQty_balance ( ( inventoryData.getQuantityIn() - inventoryData.getQuantityPull() ) + "" );

                                    inventoryLot.setFabLotNumber(inventoryData.getFabLotNumber());
                                    //System.out.println( "inventoryLot.setFabLotNumber(inventoryData.getFabLotNumber()); = " +inventoryData.getFabLotNumber() );
                                    int temp_lot_Id = -1;
                                    temp_lot_Id = LotDAO.savePackingSlipInventoryLot ( null , inventoryLot );
                                    if( temp_lot_Id != -1 )
                                    {
                                          inventoryData.setLotId ( temp_lot_Id );
                                    }
                                    else
                                    {
                                        throw new ScalarException( "Could not create new Lot "+ inventoryData.getLotNumber() );
                                    }
                                    //inventoryLot 	  = new InventoryLot ( location.getLocationName() , Constants.LOCATION_TYPE_FINISHED_GOODS, inventoryData.getLotNumber(), inventoryData.getMktPartNumber(), StringUtils.formatDateToString( inventoryData.getDateIn() ) , inventoryData.getQuantityIn()+"", inventoryData.getDateCode() );
                                    /*if(inventoryData.getQuantityIn() == inventoryData.getQuantityPull())
                                    {
                                        inventoryLot = new InventoryLot ( location , Constants.LOCATION_TYPE_FINISHED_GOODS, inventoryData.getLotNumber(), inventoryData.getMktPartNumber(), StringUtils.formatDateToString( inventoryData.getDateIn() ) , inventoryData.getQuantityIn() +"", inventoryData.getDateCode() );
                                        inventoryLot.setQtyOut(inventoryData.getQuantityPull()+"");
                                    }
                                    else
                                    {
                                        inventoryLot = new InventoryLot ( location , Constants.LOCATION_TYPE_FINISHED_GOODS, inventoryData.getLotNumber(), inventoryData.getMktPartNumber(), StringUtils.formatDateToString( inventoryData.getDateIn() ) , ( inventoryData.getQuantityIn() - inventoryData.getQuantityPull() ) +"", inventoryData.getDateCode() );
                                    }
									inventoryLotList.add( inventoryLot ); */
                                }
                                else
                                {
                                    throw new ScalarException( "coud not Fetch vendor name to create New Lots for DropShippment" );
                                }
                            }
                        }
                    }
                 }
                 /*if( inventoryLotList != null && inventoryLotList.isEmpty() == false )
                     {
                        LotDAO.saveInventoryLot(null, inventoryLotList.iterator(), "", "" );
                        //System.out.println( "Updated successfully into database" );
                     }

                    finalInventoryMapIterator 	= finalInventoryMap.keySet().iterator();
                    while( finalInventoryMapIterator.hasNext() )
                    {
                        finalInventoryDataList 		= ( ArrayList )finalInventoryMap.get( finalInventoryMapIterator.next() );
                        if( finalInventoryDataList != null )
                        {
                            finalInventoryDataListSize = finalInventoryDataList.size();
                            for( int j = 0; j < finalInventoryDataListSize; j++ )
                            {
                                inventoryData = (FinalInventoryData) finalInventoryDataList.get ( j ) ;
                                if( inventoryData.getLotId() == -1 )
                                {
                                    lotId = -1;
                                    vendorLocationId = inventoryData.getLocationId();
                                    lotNumber		 = inventoryData.getLotNumber();
                                    partNumber		 = inventoryData.getMktPartNumber();
                                    lotId 			 = getLotIdByProcessType( null, vendorLocationId, lotNumber, Constants.LOCATION_TYPE_FINISHED_GOODS, partNumber );
                                     //System.out.println( "LotID of the New Lot "+ lotId );
                                    if( lotId != -1 )
                                    {
                                        inventoryData.setLotId( lotId );
                                    }
                                }
                            }
                        }
                    } */
            }
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException( "coud not create New Lots for DropShippment" );
        }
    }

    /**
     * @param vendorAddressId
     * @param lotNumber
     * @param processType
     * @return
     */
    /*private static int getLotIdByProcessType( DBTransaction dbTransaction, int vendorAddressId, String lotNumber, String processType, String partNumber )	throws ScalarException
	{
		Connection conn 		= null;
		PreparedStatement pstmt = null;
		ResultSet rs			= null;
		int lotId				= -1;
		int processStepId		= -1;
		int locationId			= -1;
		try
		{
			if( dbTransaction == null )
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}
			//pstmt = conn.prepareStatement( "select Product.ProcessStep_Id from Product, Process_Step where Product.ProcessStep_Id = Process_Step.ProcessStep_Id and Part_Number = ? and PS_Type_Name = ? " );
            pstmt = conn.prepareStatement( SqlQueryMappings.get ( "operations.packingslip.getProcessStepIdByPartNoAndPSTypeName" ) ); // Added by Krishna 28 June 2004, SqlQueryMappings

			pstmt.setString	( 1, partNumber );
			pstmt.setString	( 2, processType );

			rs = pstmt.executeQuery();

			if( rs.next() )
			{
				processStepId = rs.getInt( "ProcessStep_Id" );
			}
			//System.out.println( "Process step id for the new lot "+ processStepId );
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

			//pstmt = conn.prepareStatement( "select Location_Id from Location where Address_id = ? and L_Type = ?" );
            pstmt = conn.prepareStatement( SqlQueryMappings.get ( "operations.packingslip.getLocationIdByAddressIdAndLocType" ) ); // Added by Krishna 28 June 2004, SqlQueryMappings

			pstmt.setInt	( 1, vendorAddressId );
			pstmt.setString	( 2, processType );

			rs = pstmt.executeQuery();

			if( rs.next() )
			{
				locationId = rs.getInt( "Location_Id" );
			}
			//System.out.println( "Location_Id for the new lot "+ locationId );
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

			pstmt = conn.prepareStatement( SqlQueryMappings.get ( "operations.process.lot.lotNumber" ) );
			pstmt.setString( 1, lotNumber );
			pstmt.setInt( 2, locationId );
			pstmt.setInt( 3, processStepId );

			rs = pstmt.executeQuery();

			if( rs.next() )
			{
				lotId = rs.getInt( "lot_id" );
			}

		}
		catch( Exception sqle )
		{
			sqle.printStackTrace();
			throw new ScalarException( "Could not fetch the lotId for the New Lot inserted through Drop Shippment", sqle );
		}
		finally
		{
			if( rs != null )
			{
				try { rs.close(); } catch (SQLException ignore) {}
			}

			if( pstmt != null )
			{
				try { pstmt.close(); } catch (SQLException ignore) {}
			}

			if( conn != null )
			{
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}
        return lotId;
    }
	*/
    /**
     * @param lineItemList
     * @return
     */
    public static String getMadeInCountryForPartNumber( ArrayList lineItemList ) throws ScalarException
    {
        String madeInCountry = "";
        Connection conn 		= null;
        PreparedStatement pstmt = null;
        ResultSet rs			= null;
        String country			= null;
        CustomerShippingLineItems cslt = null;
        ArrayList productIdList = new ArrayList();  // This is required to check if the Product Exists Twice in the LineItems

        try
        {
            conn = DataSource.getConnection();
            //pstmt = conn.prepareStatement( "select Parameter_Value from Product, Process_Parameter where Product.ProcessStep_Id = Process_Parameter.ProcessStep_Id and Product_Id = ? and Parameter_Key = ? " );
            pstmt = conn.prepareStatement( SqlQueryMappings.get ( "operations.packingslip.loadParameterValuesByProductIdAndParameterKey" ) ); // Added by Krishna 28 June 2004, SqlQueryMappings

            for ( int i = 0; i < lineItemList.size(); i++ )
            {
                cslt = ( CustomerShippingLineItems )lineItemList.get( i );
                if( cslt.getQtyPulled() > 0 && !( productIdList.contains( cslt.getProduct_ID()+"" ) ) )
                {
                    productIdList.add( cslt.getProduct_ID()+"" );
                    pstmt.setInt   ( 1, cslt.getProduct_ID() );
                    pstmt.setString( 2, "Made in Country" );
                    rs = pstmt.executeQuery();
                    if( rs.next() )
                    {
                        country = rs.getString( "Parameter_Value" );
                        if( country != null && country.trim().length() > 0 )
                        {
                            madeInCountry = madeInCountry + cslt.getPart_Number() + "( Country of Origin: " + country.trim() + " )\n";
                        }
                    }

                    rs.close();
                    rs = null;
                    pstmt.clearParameters();
                }
            }
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not fetch the Made in country for the LineItems", sqle );
        }
        finally
        {
            if( rs != null )
            {
                try { rs.close(); } catch (SQLException ignore) {}
            }

            if( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }

            if( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return madeInCountry;
    }

    public static ArrayList loadSubstitutablePartNumbersByCsLineItemId ( DBTransaction dbTransaction, int csLineItemId , String lineItemPartNumber , String status, int productId ) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ResultSet rs = null;
        ArrayList partNumberList = new ArrayList();
        int processStepId = -1;
        String tmpPartNumber    = "";
        String dbPartNumber     = "";
        //String dbParentPartNumber     = "";
        String productType      = "";

        try {

            ArrayList shippedPartNumbers = loadShippedPartNumbersByCsLineItemId ( dbTransaction , csLineItemId, lineItemPartNumber , "view");
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement ( SqlQueryMappings.get ("operations.packingslip.getProcessStepId") );
            pstmt.setInt (1, productId);
            rs = pstmt.executeQuery();
            if ( rs.next() )
            {
                productType = rs.getString( "Product_Type" );
                if( productType != null && productType.equalsIgnoreCase("chip") )
                {
                    processStepId = rs.getInt("ProcessStep_ID");
                }
                else
                {
                   processStepId = rs.getInt("Parent_ProcessStep_ID");
                   if( processStepId <= 0 )
                   {
                        processStepId = rs.getInt("ProcessStep_ID");
                   }
                }
            }

            rs.close();
            rs = null;
            pstmt.close();
            pstmt = null;

            /*if ( lineItemPartNumber != null )
            {
                partNumberList.add ( new LabelValueBean( lineItemPartNumber , lineItemPartNumber ) );
            }
            pstmt = conn.prepareStatement(  SqlQueryMappings.get ("operations.packingslip.loadSubstitutablePartNumbersByCsLineItemId") );
            pstmt = conn.prepareStatement( "SELECT * FROM fn_load_substitutable_part_numbers( ?, ? )" );
            pstmt = conn.prepareStatement( "Select Product_ID, ProcessStep_ID, Part_Number From Product P Where P.ProcessStep_ID IN ( Select PST.Substitute_Product_ProcessStep_Id From Product_Substitutables_Table PST Where PST.Product_ProcessStep_Id = ? )" );
            */

            pstmt = conn.prepareStatement( SqlQueryMappings.get ("operations.packingslip.loadSubstitutablePartNumbersByCsLineItemId") );
            pstmt.setInt ( 1, processStepId );
            //pstmt.setInt ( 1, csLineItemId);
            rset = pstmt.executeQuery();
            while ( rset.next() )
            {
                dbPartNumber        = rset.getString ("Part_Number");
                //dbParentPartNumber  = rset.getString ("Parent_Part_Number");
                if( ! lineItemPartNumber.equalsIgnoreCase( dbPartNumber ) )
                {
                    partNumberList.add ( new LabelValueBean( dbPartNumber , dbPartNumber ) );
                }
                if(shippedPartNumbers != null && shippedPartNumbers.contains( dbPartNumber ) )
                {
                   shippedPartNumbers.remove( dbPartNumber + "" );
                }
            }

            //dbParentPartNumber = null;
            rset.close();
            rset = null;
            pstmt.close();
            pstmt = null;
            //pstmt = conn.prepareStatement ( "SELECT P1.Part_Number, ISNULL(P_Type,'CHIP') AS Product_Type, (SELECT P2.Part_Number FROM Product P2 where P2.Product_ID = P1.P_Parent_Product_ID) AS Parent_Part_Number FROM Product P1 WHERE P1.Part_Number = ?" );
            if( shippedPartNumbers != null )
            {
                for ( int i = 0; i < shippedPartNumbers.size() ; i++ )
                {
                    tmpPartNumber = ( String ) shippedPartNumbers.get( i );
                    if( tmpPartNumber != null &&  tmpPartNumber.trim().length() > 0 && ! lineItemPartNumber.equalsIgnoreCase( tmpPartNumber ))
                    {
                        partNumberList.add( new LabelValueBean( tmpPartNumber , tmpPartNumber ) );
                    }
                }
            }
        }
        catch (Exception e) {
            throw new ScalarException ("Could not retrieve Substitutable PartNumbers from database", e);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException ignore) {}
            }
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return partNumberList;
    }

    public static ArrayList loadShippedPartNumbersByCsLineItemId ( DBTransaction dbTransaction, int csLineItemId , String lineItemPartNumber , String status ) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList partNumberList = new ArrayList();

        String tmpPartNumber = "";

        try {

            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            if ( status.equalsIgnoreCase("open") == false  )
            {
                pstmt = conn.prepareCall (SqlQueryMappings.get ("operations.packingslip.loadShippedPartNumbersByCsLineItemId"));
                pstmt.setInt (1, csLineItemId);
                rset = pstmt.executeQuery();

                if ( rset.next() )
                {
                    tmpPartNumber = rset.getString ( "CSLIT_Shipped_Part_Number" ) ;

                    if ( tmpPartNumber != null && tmpPartNumber.trim().length() > 0 )
                    {
                        StringTokenizer st = new StringTokenizer ( tmpPartNumber, "/" );
                        while (st.hasMoreTokens() == true)
                        {
                           partNumberList.add ( st.nextToken() );
                        }
                    }
                }
            }
            /*if ( partNumberList.size() == 0 )
            {
                if (lineItemPartNumber != null && lineItemPartNumber.trim().length() > 0 )
                {
                    partNumberList.add ( lineItemPartNumber );
                }
            } */
        }
        catch (Exception e) {
            throw new ScalarException ("Could not retrieve Shipped PartNumbers list from database", e);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
        return partNumberList;
    }

    public static ArrayList getShippedPartNumbersByCsLineItemId ( DBTransaction dbTransaction, int csLineItemId , String lineItemPartNumber , String status ) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList partNumberList = new ArrayList();

        String tmpPartNumber = "";
        String[] partNumbers = null;

        try {

            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            //pstmt = conn.prepareCall ( "SELECT DISTINCT P.Part_Number, P.ProcessStep_ID FROM Product P INNER JOIN Lot_Table LT ON P.ProcessStep_ID = LT.ProcessStep_ID INNER JOIN Customer_Shipping_Lot_Number_Table CSLNT ON LT.Lot_ID = CSLNT.Lot_ID WHERE CSLNT.CSLIT_ID = ? and CSLNT.CSLNT_Qty_Pull > 0" );
            pstmt = conn.prepareCall ( SqlQueryMappings.get( "operations.packingslip.loadInvPulledPartNumbersByCsLineItemId" ) );

            pstmt.setInt (1, csLineItemId);
            rset = pstmt.executeQuery();

            while ( rset.next() )
            {
                partNumberList.add( rset.getString ( "Part_Number" ) ) ;
                //partNumberList.add( new LabelValueBean( rset.getString ( "Part_Number" ) , rset.getString("ProcessStep_ID") ) ) ;
                //partNumberList.add( rset.getString ( "ProcessStep_ID" ) ) ;
            }

            /*partNumbers = new String[ partNumberList.size() ];
            for( int i = 0 ; i < partNumberList.size() ; i++ )
            {
                partNumbers[i] = ( String ) partNumberList.get(i);
            } */
        }
        catch (Exception e) {
            throw new ScalarException ("Could not retrieve Shipped PartNumbers list from database", e);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return partNumberList;
    }

    public static String getShippedPartNumberString ( String[] shippedPartNumber , String status ) throws ScalarException
    {

        String[]     shippedPartNo = null;
        StringBuffer shippedPartNo1 = new StringBuffer();
        String       shippedPartNo2 = null;

        try
        {
            shippedPartNo = shippedPartNumber ;
            if( shippedPartNo != null && shippedPartNo.length > 0 )
            {
                for( int k = 0 ; k < shippedPartNo.length ; k++ )
                {
                    if( shippedPartNo[k] != null && shippedPartNo[k].trim().length() > 0  )
                    {
                        shippedPartNo1.append( shippedPartNo[k] );
                        shippedPartNo1.append( "/" );
                    }
                }
                if( shippedPartNo1.toString().trim().length() > 0 )
                {
                    shippedPartNo2 = shippedPartNo1.substring ( 0 , shippedPartNo1.toString().trim().length() - 1 );
                }
            }

        }
        catch (Exception e) {
            throw new ScalarException ("Could not parse the Shipped Part Numbers from database", e);
        }

        return shippedPartNo2;
    }

    /*	This method takes the Customer Shipping ID
     *  and returns hashMap of all the LineItems in that packingSlip
     *  Containing the arraylist of the Quantity pulled
     *  Used in
     *  1. NewPackingSlip action for confirm conditions
     */
    public static HashMap loadLotNumberAndLineItemDataByCSID ( int customerShippingId ) throws ScalarException
    {
        Connection conn 		= null;
        PreparedStatement pstmt = null;
        ResultSet rset 			= null;
        HashMap lineItemLotNumberDataMap = new HashMap();
        HashMap csLineItemLotNumberDataMap = new HashMap();
        ArrayList lineItems		   = null;
        FinalInventoryData fid 	   = null;
        int lineItemId			   = -1;
        int csLineItemId		   = -1;

        try
        {
            conn 	= DataSource.getConnection();
            //pstmt 	= conn.prepareCall ( "select CSLIT_ID from Customer_Shipping_Line_Item_Table where CustomerShipping_ID = ? " );
            pstmt 	= conn.prepareCall ( SqlQueryMappings.get ( "operations.packingslip.getCustomerShippingLineItemId" ) ); // Added by Krishna 28 June 2004, SqlQueryMappings
            pstmt.setInt ( 1, customerShippingId );
            rset 	= pstmt.executeQuery();

            while ( rset.next() )
            {
                lineItemLotNumberDataMap.put  ( rset.getString("LineItem_ID"), "" );
                csLineItemLotNumberDataMap.put( rset.getString("CSLIT_ID"), ""    );
            }

            rset.close();
            rset = null;
            pstmt.close();
            pstmt = null;

            pstmt = conn.prepareStatement(  SqlQueryMappings.get ( "operations.packingslip.loadLotAndQuantityPulledDetails" ) );

            Iterator it     = lineItemLotNumberDataMap.keySet().iterator();
            Iterator csIt   = csLineItemLotNumberDataMap.keySet().iterator();

            while( csIt.hasNext() )
            {
                lineItems	    = new ArrayList();
                lineItemId      = -1;
                csLineItemId    = -1;
                try{
                    //lineItemId	 = Integer.parseInt ( (String) it.next() );
                    csLineItemId = Integer.parseInt ( (String) csIt.next() );
                }catch(NumberFormatException nfe){};

                pstmt.setInt( 1, csLineItemId );
                rset = pstmt.executeQuery();

                while ( rset.next() )
                {
                    fid = new FinalInventoryData();
                    lineItemId = rset.getInt( "LineItem_ID" );
                    fid.setLotId		( rset.getInt( "Lot_ID" ) );
                    fid.setCsLineItemId ( csLineItemId );
                    fid.setQuantityPull	( rset.getInt( "CSLNT_Qty_Pull" ) );
                    fid.setLotNumber	( rset.getString( "Lot_Number" ) );
                    fid.setDateCode     ( rset.getString( "Lot_Date_Code" ) );
                    fid.setMktPartNumber( rset.getString( "Part_Number" ) );
                    fid.setLineItemId   ( lineItemId );
                    fid.setLotEnteredBy ( rset.getString( "Lot_Entered_By" ) );
                    fid.setIsReduceBalance( rset.getInt( "Lot_Entered_By" ) );
                    //System.out.println("-----------------#-GET-INV-OBJ-FROM-DB = " + fid);
                    lineItems.add( fid );
                }
                lineItemLotNumberDataMap.put ( lineItemId+"".trim(), lineItems );
            }
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not loadLotNumberDataByCustomerShippingId information" , e );
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
        return lineItemLotNumberDataMap;
    }

    /**
     * Loads the No.of Outer Boxes for given Customer Shipping Line Item .
     *
     * @param cLineItem
     * @throws ScalarException
     * @return No.Of OuterBoxes
     */
    public static int loadOuterBoxesforLineItem( CustomerShippingLineItems cLineItem ) throws ScalarException
    {
        Connection conn 		= null;
        PreparedStatement pstmt = null;
        ResultSet rset 			= null;

        int unitsPerTray        = 0;
        int traysPerInnerBox    = 0;
        int innerBoxsPerOuterBox= 0;
        int retOuterBoxes       = 0;
        try
           {
            conn 	= DataSource.getConnection();
               pstmt 	= conn.prepareCall ( SqlQueryMappings.get ( "operations.packingslip.loadProcessStepParametersByProductId" ) ); // Added by Krishna 28 June 2004, SqlQueryMappings

            int dbParameterValue 			= 0;
            String dbParameterKey 			= "";
            String dbStrParameterValue 		= null ;

            pstmt.setInt ( 1, cLineItem.getProduct_ID() );
            rset 	= pstmt.executeQuery();

            while ( rset.next() )
            {
                dbParameterValue	= 0;
                dbParameterKey 		= rset.getString( "Parameter_Key" );
                dbStrParameterValue = rset.getString( "Parameter_Value" ) ;

                if ( dbStrParameterValue != null && dbStrParameterValue.trim().length() > 0 )
                {
                    if ( dbParameterKey.equalsIgnoreCase ( "Units Per Tray" ) || dbParameterKey.equalsIgnoreCase ( "Trays Per InnerBox" ) || dbParameterKey.equalsIgnoreCase ( "InnerBoxes Per OuterBox" ) )
                    {
                        try
                        {
                            dbParameterValue = Integer.parseInt( dbStrParameterValue ) ;
                        }
                        catch (NumberFormatException nfe) {}
                    }
                }

                if( dbParameterKey.equalsIgnoreCase ( "Units Per Tray" ) )
                {
                    cLineItem.setUnitsPTrays ( dbParameterValue );
                }
                else if( dbParameterKey.equalsIgnoreCase ( "Trays Per InnerBox" ) )
                {
                    cLineItem.setTraysPInnerBoxes ( dbParameterValue );
                }
                else if( dbParameterKey.equalsIgnoreCase ( "InnerBoxes Per OuterBox" ) )
                {
                    cLineItem.setInnerPOuterBoxes ( dbParameterValue );
                }
            }
               unitsPerTray            = cLineItem.getUnitsPTrays();
               traysPerInnerBox        = cLineItem.getTraysPInnerBoxes() ;
               innerBoxsPerOuterBox    = cLineItem.getInnerPOuterBoxes();

               int totalQtyPulled 	= 0;
               int totalTrays 		= 0;
               int totalInnerBoxes = 0;
               int totalOuterBoxes = 0;

               totalQtyPulled  = cLineItem.getQtyPulled();

               if( unitsPerTray > 0 )
               {
                totalTrays  =  totalQtyPulled / unitsPerTray;
                if( totalQtyPulled % unitsPerTray > 0 )
                {
                    totalTrays++;
                }
               }
               else
               {
                totalTrays = 0;
               }

               if( ( traysPerInnerBox > 0 ) )
               {
                totalInnerBoxes =  totalTrays / traysPerInnerBox;
                if( totalTrays % traysPerInnerBox > 0 )
                {
                    totalInnerBoxes++;
                }
               }
               else
               {
                totalInnerBoxes = 0;
               }

               if( ( innerBoxsPerOuterBox > 0 ) )
               {
                totalOuterBoxes = totalInnerBoxes / innerBoxsPerOuterBox;
                if( totalInnerBoxes % innerBoxsPerOuterBox > 0 )
                {
                    totalOuterBoxes++;
                }
               }
               else
               {
                totalOuterBoxes = 0;
               }

            cLineItem.setTotalTrays     ( totalTrays + "" );
            cLineItem.setTotalInnerBoxes( totalInnerBoxes + "" );
            cLineItem.setTotalOuterBoxes( totalOuterBoxes + "" );
            if( cLineItem.getTotalOuterBoxes() != null )
            {
                retOuterBoxes = Integer .parseInt( cLineItem.getTotalOuterBoxes() );
            }
        }
           catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not loadLotNumberDataByCustomerShippingId information" , e );
        }
        finally
        {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
        return retOuterBoxes;
    }

    /**
     * @param packingSlipForm
     * @return
     */
    public static boolean isSoftWarePackingSlip( PackingSlipForm packingSlipForm ) throws ScalarException
    {
        boolean isSoftWarePS 	= false;
        Connection conn 		= null;
        PreparedStatement pstmt = null;
        ResultSet rset 			= null;
        //ArrayList lineItems		= null;
        //CustomerShippingLineItems cslt = null;
        //int customerShippingId	= packingSlipForm.getCustomerShippingId();

        try
        {
            conn 	= DataSource.getConnection();
            pstmt 	= conn.prepareStatement( "select SO_Is_Sample from sales_order where SalesOrder_Id =(Select SalesOrder_ID FROM Customer_Shipping_Table WHERE CustomerShipping_ID = ?)" );
            pstmt.setInt( 1, packingSlipForm.getCustomerShippingId() );
            rset = pstmt.executeQuery();

            if( rset.next() )
            {
                isSoftWarePS = rset.getInt( "SO_Is_Sample" )==2?true:false;
            }
            //Commented by sujan on 29th nov 2004 as we don't need to set the additional properties for the lineitems when it is a software
            /* if( isSoftWarePS )
               {
                   if (rset != null) {
                       try { rset.close(); } catch (SQLException ignore) {}
                   }
                   if (pstmt != null) {
                       try { pstmt.close(); } catch (SQLException ignore) {}
                   }

                 lineItems = packingSlipForm.getLineitems();

                   if( lineItems != null && lineItems.isEmpty() == false )
                   {
                       for( int i = 0; i < lineItems.size(); i++ )
                       {
                           cslt = (CustomerShippingLineItems)lineItems.get(i);
                           pstmt = conn.prepareStatement( "SELECT COOL_Category, COOL_Start_Mac_Address, COOL_End_Mac_Address, CSLIT_URL FROM CUSTOMER_OPEN_ORDER_LINEITEM, CUSTOMER_SHIPPING_LINE_ITEM_TABLE WHERE CUSTOMER_SHIPPING_LINE_ITEM_TABLE.LINEITEM_ID = CUSTOMER_OPEN_ORDER_LINEITEM.LINEITEM_ID AND CUSTOMER_SHIPPING_LINE_ITEM_TABLE.LINEITEM_ID = ? AND CUSTOMERSHIPPING_ID = ?" );
                           pstmt.setInt( 1, cslt.getLineItemId() );
                           pstmt.setInt( 2, customerShippingId );
                           rset = pstmt.executeQuery();

                           if( rset.next() )
                           {
                               cslt.setCategory( rset.getString( "COOL_Category" ) );
                               cslt.setStartMacAddress( rset.getString( "COOL_Start_Mac_Address" ) );
                               cslt.setEndMacAddress( rset.getString( "COOL_End_Mac_Address" ) );
                               cslt.setLineItemUrl( rset.getString( "CSLIT_URL" ) );
                           }
                           if (rset != null) {
                               try { rset.close(); } catch (SQLException ignore) {}
                           }
                           if (pstmt != null) {
                               try { pstmt.close(); } catch (SQLException ignore) {}
                           }
                       }
                   }
               } */
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not check SoftWare Product" , e );
        }
        finally
        {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
        return isSoftWarePS;
    }

    /**
     * @param salesOrderId
     * @return
     */
    public static boolean isSoftWareSalesOrder( int salesOrderId ) throws ScalarException
    {
        boolean isSoftWareSO 	= false;
        Connection conn 		= null;
        PreparedStatement pstmt = null;
        ResultSet rset 			= null;

        try
        {
            conn 	= DataSource.getConnection();
            pstmt 	= conn.prepareStatement( "select SO_Is_Sample from sales_order where SalesOrder_Id = ?" );
            pstmt.setInt( 1, salesOrderId );
            rset = pstmt.executeQuery();

            if( rset.next() )
            {
                isSoftWareSO = rset.getInt( "SO_Is_Sample" )==2?true:false;
            }
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not check SoftWare Sales Order" , e );
        }
        finally
        {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
        return isSoftWareSO;
    }

    /**
     * @param shippingId
     * @return
     */
    public static boolean isShippingIdCancelled( int shippingId ) throws ScalarException
    {
        boolean isCancelled 	= false;
        Connection conn 		= null;
        PreparedStatement pstmt = null;
        ResultSet rset 			= null;

        try
        {
            conn 	= DataSource.getConnection();
            pstmt 	= conn.prepareStatement( "select cst_status from customer_shipping_table where CustomerShipping_ID = ?" );
            pstmt.setInt( 1, shippingId );
            rset = pstmt.executeQuery();
            String status = null;
            if( rset.next() )
            {
                status = rset.getString( "cst_status" );
            }

            if ( "CANCELLED".equalsIgnoreCase( ( status == null ) ? "" : status.trim() ))
            {
                 isCancelled = true;
            }
            else
            {
                isCancelled = false;
            }
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not check SoftWare Sales Order" , e );
        }
        finally
        {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
        return isCancelled;
    }



    public static int getCstShippingIdByRandomKey(int cstRandomKey) throws ScalarException
    {

        int cstShipId = -1 ;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;

        try
        {
            conn = DataSource.getConnection();
            //pstmt = conn.prepareStatement( "select CustomerShipping_ID from Customer_Shipping_Table where CST_Random_Key = ?" );
            pstmt = conn.prepareStatement( SqlQueryMappings.get ( "operations.packingslip.getUniqueRandomKey" ) );
            pstmt.setInt ( 1 , cstRandomKey );
            rset = pstmt.executeQuery();

            if( rset.next() )
            {
                cstShipId = rset.getInt( "CustomerShipping_ID" );
            }
        }
        catch( Exception e )
        {
            throw new ScalarException( "Could not retrieve Shipping Id with Random Number from database", e );
        }
        finally
        {
            if( rset != null )
            {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return cstShipId;
    }

    public static void getLineItemScheduleNumberAndDescription( LineItem lineItem, int customerShippingId) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset 	= null;
        String key 		= null;
        String value 	= null;

        try
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.packingslip.getLineItemScheduleNumberAndDescription" ) );

            pstmt.setInt( 1, customerShippingId );
            pstmt.setInt( 2, lineItem.getLineItemId() );

            rset = pstmt.executeQuery();

            if( rset.next() )
            {
                lineItem.setScheduleNumber( rset.getString( "Schedule_Number" ) );
                lineItem.setDescription( rset.getString( "Description" ) );
                lineItem.setCustomerPartNumber( rset.getString ("Customer_Part_Number") );
                lineItem.setOdmPartNo( rset.getString ("CSLIT_ODM_Part_Number") );
                /*scheduleNumber 	= rset.getString( "Schedule_Number" );
                    description 	= rset.getString( "Description" );
                    cust_part_num 	= rset.getString ("Customer_Part_Number");*/
            }

            if (rset != null) {	try { rset.close(); } catch (SQLException ignore){}	}
            if (pstmt != null) { try { pstmt.close(); } catch (SQLException ignore){} }
            
            pstmt = conn.prepareStatement( "select Parameter_Key, Parameter_value from Process_Parameter where ProcessStep_ID =( Select ProcessStep_Id from Product where product_id = ? )" );
            pstmt.setInt( 1, lineItem.getProductId() );
            rset = pstmt.executeQuery();

            lineItem.setScheduleNumber( Constants.COMMERCIAL_INVOICE_HTS_CODE );
            lineItem.setLicenseException( "" );
            lineItem.setUom( "" );
            lineItem.setScheduleDescription( Constants.COMMERCIAL_INVOICE_HTS_DESCRIPTION);
            lineItem.setEccn( Constants.COMMERCIAL_INVOICE_ECCN);

            while( rset.next() )
            {
                key = rset.getString( 1 );
                value = rset.getString( 2 );

                if( key != null )
                {
                    if( "Harmonized Traffic Code".equalsIgnoreCase( key ) )
                    {
                        if (ConfigManager.getBooleanProperty(ConfigManager.SHOW_PROCESS_PARAM_ECCNHTS_CODE_IN_PACKINGSLIP ) )
                        {
                            lineItem.setScheduleNumber( value != null?value:Constants.COMMERCIAL_INVOICE_HTS_CODE );
                        }
                    }
                    else if( "HTS Description".equalsIgnoreCase( key ) )
                    {
                        if (ConfigManager.getBooleanProperty(ConfigManager.SHOW_PROCESS_PARAM_ECCNHTS_CODE_IN_PACKINGSLIP ) )
                        {
                            lineItem.setScheduleDescription( value != null?value:Constants.COMMERCIAL_INVOICE_HTS_DESCRIPTION );
                        }
                    }
                    else if( "License Exception".equalsIgnoreCase( key ) )
                    {
                        lineItem.setLicenseException( value != null?value:"" );
                    }
                    else if( "ECCN".equalsIgnoreCase( key ) )
                    {
                        if (ConfigManager.getBooleanProperty(ConfigManager.SHOW_PROCESS_PARAM_ECCNHTS_CODE_IN_PACKINGSLIP ) )
                        {
                            lineItem.setEccn( value != null?value:Constants.COMMERCIAL_INVOICE_ECCN );
                        }
                    }
                    else if( "UOM".equalsIgnoreCase( key ) )
                    {
                        lineItem.setUom( value != null?value:"" );
                    }
                }
            }
        }
        catch( Exception sqle )
        {
            throw new ScalarException( "Could not get LineItem Schedule Number for Customer Shipping ID: " + customerShippingId + ", LineItem ID: " + lineItem.getLineItemId(), sqle );
        }
        finally
        {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }

    public static void mergeDBInventoryDataMapIntoExistMap ( HashMap existingFinalInventoryDataListMap , HashMap dbFinalInventoryDataListMap )
    {
        ArrayList existingInventoryDataList = null;
        ArrayList dbInventoryDataList = null;
        FinalInventoryData existingFIData = null;
        FinalInventoryData dbFIData = null;
        int existingListSize = 0;
        int dbListSize = 0;
        //boolean lotExist = false;
        String lineItemId                   = null;
        //HashMap inventoryDataListMap        = new HashMap();
        HashMap missedLotNumbersMap        = null;

        if( existingFinalInventoryDataListMap != null && existingFinalInventoryDataListMap.isEmpty() == false && dbFinalInventoryDataListMap != null && dbFinalInventoryDataListMap.isEmpty() == false )
        {
            Iterator it = existingFinalInventoryDataListMap.keySet().iterator();
            while( it.hasNext() )
            {
                lineItemId	= (String) it.next() ;

                missedLotNumbersMap = new HashMap();
                existingListSize = 0; dbListSize = 0;

                existingInventoryDataList = ( ArrayList ) existingFinalInventoryDataListMap.get( lineItemId );
                dbInventoryDataList =  ( ArrayList ) dbFinalInventoryDataListMap.get( lineItemId );

                if( dbInventoryDataList != null && dbInventoryDataList.size() > 0 )
                {
                    existingListSize = existingInventoryDataList.size();
                    dbListSize = dbInventoryDataList.size();

                    for ( int j = 0; j < dbListSize; j++ )
                    {
                        dbFIData = ( FinalInventoryData ) dbInventoryDataList.get( j );
                        //missedLotNumbersMap.put( dbFIData.getLotNumber() , "" );
                        missedLotNumbersMap.put( dbFIData.getLotId() + "" , "" );
                    }
                    for ( int i = 0; i < existingListSize; i++ )
                    {
                       existingFIData = ( FinalInventoryData ) existingInventoryDataList.get( i );
                       if( missedLotNumbersMap.containsKey( existingFIData.getLotId() + "" ) )
                       {
                            missedLotNumbersMap.remove( existingFIData.getLotId() + "" );
                       }
                    }
                    Iterator missedLotNoIT = missedLotNumbersMap.keySet().iterator();
                    String missedLotNo =  null;
                    while( missedLotNoIT.hasNext() )
                    {
                        missedLotNo	= (String) missedLotNoIT.next();
                        for ( int j = 0; j < dbListSize; j++ )
                        {
                            dbFIData = ( FinalInventoryData ) dbInventoryDataList.get( j );
                            if( missedLotNo.equalsIgnoreCase( dbFIData.getLotId() + "" ) )
                            {
                               dbFIData.setIsReduceBalance( 0 );
                               dbFIData.setQuantityPull( 0 );
                               existingInventoryDataList.add(dbFIData);
                            }
                        }
                    }
                }
            }
        }
    }

    public static HashMap getFinalInventoryMapByLineItemId( HashMap finalInventoryMap )
    {
        HashMap finalInventoryMapByCustomerShippingId = new HashMap();
        ArrayList dataList = null;
        FinalInventoryData inventoryData = null;
        Iterator it = finalInventoryMap.keySet().iterator();
        while ( it.hasNext() )
        {
            dataList = (ArrayList)finalInventoryMap.get( it.next() );

            if ( dataList.size() >= 1 )
            {
                inventoryData 	= (FinalInventoryData)dataList.get( 0 );
                finalInventoryMapByCustomerShippingId.put( inventoryData.getLineItemId() +"", dataList );
            }
        }

        return finalInventoryMapByCustomerShippingId;
    }

    public static Address loadShippingAddressDetails( DBTransaction dbTransaction, int addressId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        //boolean success = false;
        Address addressForm = new  Address();

        try
        {
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement(SqlQueryMappings.get("sales.customer.getAddress"));
            pstmt.setInt( 1, addressId );
            rs = pstmt.executeQuery();
            if(rs.next())
            {
                addressForm.setContactPerson( rs.getString("Contact_Person") );
                addressForm.setAddress1     ( rs.getString("Address_1") );
                addressForm.setAddress2     ( rs.getString("Address_2") );
                addressForm.setAddress3     ( rs.getString("Address_3") );
                addressForm.setAddress4     ( rs.getString("Address_4") );
                addressForm.setCity         ( rs.getString("City") );
                addressForm.setState        ( rs.getString("State") );
                addressForm.setZipCode      ( rs.getString("ZipCode") );
                addressForm.setCountry      ( rs.getString("Country") );
                addressForm.setPhoneNumber  ( rs.getString("Phone_Number") );
                addressForm.setCellNumber   ( rs.getString("Cell_Number") );
                addressForm.setFaxNumber    ( rs.getString("Fax_Number") );
                addressForm.setEmail        ( rs.getString("Email") );
            }
            //success = true;
        } catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException("Could not get Shipping Address from database", sqle);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
        return addressForm;
    }

    public static boolean isPackingSlipInvNoExist ( DBTransaction dbTransaction, String invoiceNo ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;

        if ( invoiceNo == null )
            return false;

        boolean invNoExists = false;

        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn  = DataSource.getConnection();
            }
            pstmt = conn.prepareStatement ( "SELECT COUNT(*) FROM CUSTOMER_SHIPPING_TABLE WHERE INV_NO = ?" );
            pstmt.setString ( 1, invoiceNo );
            rset  = pstmt.executeQuery();

            if ( rset.next() )
            {
                int count = rset.getInt ( 1 );
                if( count > 0 )
                {
                    invNoExists = true;
                }
            }
        }
        catch (Exception sqle)
        {
            throw new ScalarException ("Could not check whether " + invoiceNo + " exists or not", sqle);
        }
        finally
        {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

        return invNoExists;
    }

    /* This method is used to fetch all the PackingSlips for the Customer under that Particular
      * Status of the Packing Slip, If customerId is Zero then all the packing slips will be fetched.      
      */

    public static ArrayList loadPackingSlipsByCustomerId ( int customerId, String status ) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList packingSlipList = new ArrayList();

        try {
            conn = DataSource.getConnection();

            if ( customerId > 0 )
            {
                pstmt = conn.prepareCall ( "SELECT LABEL, CUSTOMERSHIPPING_ID FROM CUSTOMER_SHIPPING_TABLE CST, SALES_ORDER SO WHERE CST.SALESORDER_ID = SO.SALESORDER_ID AND SO.CUSTOMER_ID = ? AND CST.CST_STATUS = ? ORDER BY CST_SHIP_DATE DESC" );
                pstmt.setInt (1, customerId);
                pstmt.setString (2, status);
            }
            else
            {
                pstmt = conn.prepareCall ( "SELECT LABEL, CUSTOMERSHIPPING_ID FROM CUSTOMER_SHIPPING_TABLE CST WHERE CST.CST_STATUS = ? ORDER BY CST_SHIP_DATE DESC" );
                pstmt.setString ( 1, status );
            }
            rset = pstmt.executeQuery();

            while (rset.next())
            {
                LabelValueBean valueBean = new LabelValueBean( rset.getString ("LABEL"), rset.getInt ("CUSTOMERSHIPPING_ID")+"" );
                packingSlipList.add ( valueBean );
            }
        }
        catch (Exception e) {
            throw new ScalarException ("Could not retrieve PackingSlip list from database", e);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return packingSlipList;
    }

    public static ArrayList loadWeightMeasurements()
    {
        ArrayList weightMeasurementList = new ArrayList();
        String weightMeasurementLbs     = null;
        String weightMeasurementKgs     = null;
        try
		{
			 weightMeasurementLbs = MessageResources.getMessageResources( Labels.APPLICATION_RESOURCE_PROPERTIES ).getMessage( "label.packingSlip.WeightMeasurements" );
		}
		catch ( NullPointerException e )
		{
			 weightMeasurementLbs = Labels.DEFAULT_WEIGHT_MEASUREMENT_LABEL;
		}
        try
		{
			 weightMeasurementKgs = MessageResources.getMessageResources( Labels.APPLICATION_RESOURCE_PROPERTIES ).getMessage( "label.packingSlip.WeightKgsMeasurements" );
		}
		catch ( NullPointerException e )
		{
			 weightMeasurementKgs = Labels.DEFAULT_WEIGHT_MEASUREMENT_KGS_LABEL;
		}

        weightMeasurementList.add( new LabelValueBean( weightMeasurementLbs, weightMeasurementLbs ) );
        weightMeasurementList.add( new LabelValueBean( weightMeasurementKgs, weightMeasurementKgs ) );

        return weightMeasurementList;
    }

    public static ArrayList loadUomPkgDimensionList()
    {
        ArrayList pgkMeasurementList = new ArrayList();
        String pgkMeasurementIns     = null;
        String pgkMeasurementCms     = null;
        try
		{
			 pgkMeasurementIns = MessageResources.getMessageResources( Labels.APPLICATION_RESOURCE_PROPERTIES ).getMessage( "label.packingSlip.PackageInchesMeasurement" );
		}
		catch ( NullPointerException e )
		{
			 pgkMeasurementIns = Labels.DEFAULT_WEIGHT_MEASUREMENT_LABEL;
		}
        try
		{
			 pgkMeasurementCms = MessageResources.getMessageResources( Labels.APPLICATION_RESOURCE_PROPERTIES ).getMessage( "label.packingSlip.PackagesCmsMeasurements" );
		}
		catch ( NullPointerException e )
		{
			 pgkMeasurementCms = Labels.DEFAULT_WEIGHT_MEASUREMENT_KGS_LABEL;
		}

        pgkMeasurementList.add( new LabelValueBean( pgkMeasurementIns, pgkMeasurementIns ) );
        pgkMeasurementList.add( new LabelValueBean( pgkMeasurementCms, pgkMeasurementCms ) );

        return pgkMeasurementList;
    }

    public static String generateNextUniqueInvoiceNo( String invoiceNo ) throws ScalarException
    {
        String yearTwoDigits    = invoiceNo.substring( 0, 2 );
        String monthDigits      = invoiceNo.substring( 2, 4 );
        int tempInvoiceNo       = 0;
        String invoiceNoStr     = "0001";

        try
        {
            try
            {
                tempInvoiceNo = Integer.parseInt( invoiceNo.substring( 4 ) );
                tempInvoiceNo = tempInvoiceNo + 1;

                if( tempInvoiceNo > 9999 )
                {
                    invoiceNoStr = "0001";
                }
                else if( tempInvoiceNo > 999 )
                {
                   invoiceNoStr = tempInvoiceNo + "";
                }
                else if( tempInvoiceNo > 99 && tempInvoiceNo <= 999 )
                {
                    invoiceNoStr = "0" + tempInvoiceNo;
                }
                else if( tempInvoiceNo >= 10 && tempInvoiceNo <= 100 )
                {
                    invoiceNoStr = "00" + tempInvoiceNo;
                }
                else if( tempInvoiceNo < 10 )
                {
                    invoiceNoStr = "000" + tempInvoiceNo;
                }
            }
            catch( Exception e )
            {
                invoiceNoStr = "0001";
            }
            invoiceNoStr = yearTwoDigits + monthDigits + invoiceNoStr;
        }
        catch (Exception e) {
            throw new ScalarException ("Could not Generate Unique Invoice No", e);
        }

        return invoiceNoStr;
    }

    public static boolean isInvoiceNoExists( DBTransaction dbTransaction, String invoiceNo ) throws ScalarException
    {
        Connection conn             = null;
        PreparedStatement pstmt     = null;
        ResultSet rset              = null;
        boolean isInvoiceNoExists   = true;

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareCall ( "Select CustomerShipping_ID From Customer_Shipping_Table Where ltrim( rtrim ( Inv_No ) ) = ?" );
            pstmt.setString( 1, invoiceNo.trim() );

            rset = pstmt.executeQuery();
            if ( rset.next() )
            {
                isInvoiceNoExists = true;
            }
            else
            {
                isInvoiceNoExists = false;
            }
        }
        catch (Exception e) {
            throw new ScalarException ("Could not check the Invoice No Validation", e);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
        return isInvoiceNoExists;
    }

    public static String getPackingSlipStatus( DBTransaction dbTransaction, int customerShippingId ) throws ScalarException
    {
        Connection conn             = null;
        PreparedStatement pstmt     = null;
        ResultSet rset              = null;
        String status               = null;

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement ( "SELECT CST_STATUS FROM CUSTOMER_SHIPPING_TABLE WHERE CUSTOMERSHIPPING_ID = ?" );
            pstmt.setInt( 1, customerShippingId );

            rset = pstmt.executeQuery();
            if ( rset.next() )
            {
                status = rset.getString( "CST_STATUS" );
            }
        }
        catch (Exception e) {
            throw new ScalarException ("Could not get PackingSlip Status", e);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
        return status;
    }

    /**
     * To check whether the packing slip is ready for confirm.
     *
     * @param dbTransaction
     * @param customerShippingId
     *
     * @return true-if packing slip is ready for confirm otherwise false.
     *
     * @throws ScalarException
     */
    public static boolean isPackingSlipReadyForConfirm(DBTransaction dbTransaction, int customerShippingId) throws ScalarException {
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rset = null;
        boolean readyForConfirm = false;

        try {
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            } else {
                conn = dbTransaction.getConnection();
            }

            cstmt = conn.prepareCall( "{ CALL SP_IS_PACKING_SLIP_READY_FOR_CONFIRM( ?, ? ) }" );
            cstmt.setInt( 1, customerShippingId );
            cstmt.registerOutParameter( 2, java.sql.Types.INTEGER );
            cstmt.executeUpdate();

            readyForConfirm = cstmt.getInt(2)==0;
        }
        catch (Exception e) {
            e.printStackTrace();
            throw new ScalarException("Could not find whether packing slip is ready for confirm or not", e);
        }
        finally {
            if (rset != null) {
                try {
                    rset.close();
                } catch (SQLException ignore) {
                }
            }
            if (cstmt != null) {
                try {
                    cstmt.close();
                } catch (SQLException ignore) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ignore) {
                }
            }
        }
        return readyForConfirm;
    }
}